Using the Offset Property to Refer to a Range

You've already seen a reference to Offset; the macro recorder used it when we were recording a relative reference. It enables you to manipulate a cell based off the location of the active cell. In this way, you don't have to know the address of a cell.

The syntax for the Offset property is Range.Offset(RowOffset, ColumnOffset)

To affect cell F5 from cell A1, write Range("A1").Offset(RowOffset:=4, ColumnOffset:=5)

or, shorter yet: Range("A1").Offset(4,5)

The count starts at A1, but does not include A1.

But what if you need to go over only a row or a column, but not both? You don't have to enter both the row and column parameter. If you need to refer to a cell one column over, use one of these:

Range("A1").Offset(ColumnOffset:=1) Range("A1").Offset(,1)

Both lines mean the same. The choice is yours. Referring to a cell one row up is similar:

Range("B2").Offset(RowOffset:=-1) Range("B2").Offset(-1)

Once again, the choice is yours. It is a matter of readability of the code.

Let's say we had a list of produce with totals next to them. Find any total equal to zero and place LOW in the cell next to it. You could do it this way:

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues) Rng.Offset(, 1).Value = "LOW"

The LOW totals are quickly noted by the program, as shown in Figure 3.1.

0 0

Post a comment