How to use Relative References In Excel

We must make sure that the macro is recorded starting from the cell where the steps must begin in order to record a macro with relative references.

Implementation

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

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

Note: The macro will place whatever you recorded on the same worksheet in the same location if you do not create a new worksheet before running it. You do not want this. Every report needs to be on a different worksheet.

Recording a Macro

The Record Macro command, located on the ribbon under the VIEW tab Macros, allows you to begin recording the macro. 

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

Stop recording the macro

Either use the Stop Recording command located on the ribbon under the VIEW tab Macros or click the Stop Recording Macro button located on the left side of the Excel taskbar to stop recording the macro.

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].

Running a Macro

Simply by running the macro, you may create any number of reports in a matter of seconds.

Step 10. Select Macro from the list (eg. relative reference) and Press “Run”.

Output

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

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 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).