The Offset property

The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that I discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows:

object.Offset(rowOffset, columnOffset)

The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or to the right), negative (up or to the left), or zero. The example that follows enters a value of 12 into the cell directly below the active cell:

ActiveCell.Offset(1,0).Value = 12

The next example enters a value of 15 into the cell directly above the active cell:

ActiveCell.Offset(-1,0).Value = 15

If the active cell is in row 1, the Offset property in the preceding example generates an error because it cannot return a Range object that doesn''t exist.

The Offset property is quite useful, especially when you use variables within looping procedures. I discuss these

When you record a macro using the relative reference mode, Excel uses the Offset property to reference cells relative to the starting position (that is, the active cell when macro recording begins). For example, I used the macro recorder to generate the following code. I started with the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.