Relative Cell References

In some cases you may want the recorded macro to work with cell references that are Relative to a particular cell. These are recorded in R1C1 notation.
For example you might want to enter some data into the active cell and also enter some data into the cell directly below.
Relative references are useful when you want to perform an action anywhere on a worksheet.
You can change to relative recording by pressing the button on the right hand side of the Stop Recording toolbar. This button is a toggle.
You can change between absolute and relative references at any time while you are recording your macro.
Be aware that there is no indication given other than the button being pushed in, as to which mode you are currently in. The first recorded macro in a workbook will always start by using absolute references.
If you stop recording when you have been using relative references the next recorded macro will start by using relative references.
Using relative references will generate code that always refers to the cell currently selected before you start recording and then uses the "Offset" method to obtain the relative cell address.


Select cell "B2" before you select (Tools > Macros > Record New Macro).
0) In the Macro Name box type "Relative".
1) Select the Relative References button on the Stop Recording toolbar.
2) Repeat exactly the same steps 2-8 as above.
3) Select the Relative References button again to switch back to Absolute References.
4) Press the Stop Recording button on the Stop Recording toolbar.

alt text

This recorded macro produces the following code:

Sub Relative() 
    ActiveCell.Offset(1, 1).Range("A1:A5").Select
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
    End With
    ActiveCell.Offset(0, 1).Range("A1:A5").Select
    With Selection.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
    End With
    Selection.Font.Italic = True
    ActiveCell.Offset(0, 1).Range("A1:A5").Select
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
End Sub

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext