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 right), negative (up or 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

By the way, 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 topics in the next chapter.

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.

Sub Macro1()

ActiveCell.FormulaR1C1 = "1" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "3" ActiveCell.Offset(-2, 0).Range("A1").Select End Sub

Notice that the macro recorder uses the FormulaR1C1 property. Normally, you'll want to use the Value property to enter a value into a cell. However, using FormulaR1C1 or even Formula produces the same result.

Also notice that the generated code references cell A1, which might seem a bit odd, because that cell was not even involved in the macro. This is a quirk in the macro recording procedure that makes the code more complex than necessary. You can delete all references to Range("A1"), and the macro still works perfectly:

Sub Modified Macro1()

ActiveCell.FormulaR1C1 = "1" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "3" ActiveCell.Offset(-2, 0).Select End Sub

In fact, here's a much more efficient version of the macro (which I wrote myself) that doesn't do any selecting:

Sub Macro1()

ActiveCell = 1 ActiveCell.Offset(1, 0) = 2 ActiveCell.Offset(2, 0) = 3 End Sub

0 0

Post a comment