Relative References in Excel Macros

We have two options to refer a cell in excel VBA Absolute references and Relative references.  Default Excel records macro in Absolute mode.

In this article, we learn about Relative references in excel VBA.  We select a cell “A1”, turn on “Use Relative Reference” and record a macro to type some text in cells B2:B4.  

Since we turn on the “Relative reference” option.  Macro considers the number of rows and number of columns from active cells.  In our example, we select cell A1 and start type B2 which is to move one column and one row from A1 (Active cell).

Implementation:

Follow the below steps to implement relative reference in Excel Macros:

Step 1: Open Excel and Select Cell “A1”.

Step 2: Go to “Developer” Tab >> Press “Use Relative References” >> Click “Record Macro” .

Step 3: Enter the Macro name “relativeReference” and Press “OK”.

Step 4: Type “Australia” in cell B2  

Step 5: Type “Brazil” in cell B3

Step 6: Type “Mexico” in cell B4

Step 7: Select cell B5 and Press “Stop Recording”  

VBA Code (Recorded):

Sub relativeReference()
    ActiveCell.Offset(1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Australia"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Brazil"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Mexico"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub 

Step 8: You just delete the contents in cells B2:B4, Select Cell B1.

Step 9: Go to View >> Macros >> View Macros – to popup Macro dialog box [keyboard shortcut – Alt+F8].

Step 10: Select Macro from list (eg. relativeReference) and Press “Run”.

Output:

The active cell is B1 and run the macro.  So, the outputs (C2:C4) are placed one row and one column from the active cell B1.

My Personal Notes arrow_drop_up

Save