Tip Recording a Selection of Cells

By default, the macro recorder selects cells using the Range property. If you turn on the macro recorder and select cell A2, enter any text, and select cell A5, you will see the following lines of code in the Visual Basic Editor window:

Range("A2").Select ActiveCell.FormulaR1C1 = "text" Range("A5").Select

You can have the macro recorder use the Offset property if you tell it to use relative references. To do this, click the Relative Reference button on the Recording toolbar prior to recording. The macro recorder produces the following lines of code:

ActiveCell.Offset(-3, 0).Range_

("A1").Select ActiveCell.FormulaR1C1 = "text" ActiveCell.Offset(3, 0).Range_ ("A1").Select

When you record a procedure using relative references, the procedure will always select a cell relative to the active cell. Notice that the first and third line in the above instructions reference cell A1, even though nothing was said about cell A1. As you remember from Chapter 1, the macro recorder has its own way of getting things done. To make the above instructions less complex, you can delete the reference to Range("A1"):

ActiveCell.Offset(-3, 0).Select ActiveCell.FormulaR1C1 = "text" ActiveCell.Offset(3, 0).Select

After recording a procedure using the Relative Reference button, don't forget to click this button again if your next procedure does not require the use of relative addressing.

The last two instructions can be combined in the following way: Selection.Offset(2, 2).Resize(2, 4).Select

In the example above, the Offset property calculates the beginning of a new range, the Resize property determines the new size of the range, and the Select method selects the specified range of cells.

0 0

Post a comment