How to Create a Relative VBA Formula Reference in Excel
Question details
The user needs to construct a relative row reference using FormulaR1C1 in an Excel VBA macro so that the generated formula can be accurately filled down multiple rows.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing a VBA macro to generate and fill formulas down a specific column using variable column numbers.
- Observed behavior
- Using absolute references (such as R3C) causes the generated formula to reference a fixed row when filled down, instead of adapting to each subsequent row dynamically.
Ensure you have the Developer tab enabled in your spreadsheet program to access the Visual Basic editor, and save your workbook as a Macro-Enabled Workbook (.xlsm) before testing new code.
Use RC Notation in FormulaR1C1 for Relative Row References
Modify your VBA expression to utilize relative references, which instructs the spreadsheet engine to automatically adjust the row number as the formula is filled down.
When using the FormulaR1C1 property in VBA, cell references can be defined absolutely or relatively. Absolute references, like 'R3C2', lock the formula to Row 3 and Column 2. To allow the formula to adapt dynamically to the row it occupies when filled down a column, you must remove the explicit number and use the relative 'RC' syntax.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet.
In the Project Explorer panel, double-click the module or worksheet containing your FormulaR1C1 script to view the code.
Locate the line where the formula is assigned. Change the absolute row reference (e.g., '=R3C') to the relative reference '=RC'. The 'R' without an accompanying number tells the formula to look at the current row dynamically.
Run your macro. Check the spreadsheet to verify that as the formula fills down the cells (for example, from F3 to F80), the row references adjust correctly for each individual row.

Easily Manage VBA and Macros with WPS Spreadsheet
WPS Office provides robust support for VBA and Macros, allowing you to seamlessly run, edit, and debug R1C1 formulas and automation scripts exactly as you would in Microsoft Excel.
- 1. Download and Install WPS Office: Get the latest version of WPS Office for free from the official website and install it on your device.
- 2. Enable Developer Tools: Open WPS Spreadsheet, go to the top menu, click on the Developer tab, and select the Visual Basic editor to access your macros.
- 3. Run Your VBA Code: Paste or open your workbook containing the adjusted FormulaR1C1 code and run it to automatically execute your relative formula logic.

Frequently Asked Questions
What is the difference between R1C1 and A1 reference styles in VBA?
The A1 style uses letters for columns and numbers for rows (e.g., A1), which is what most users see on the grid. The R1C1 style uses numbers for both rows and columns (e.g., R1C1). R1C1 is highly advantageous in VBA loops because you can use integer variables to easily calculate and reference relative rows and columns.
How do I specify a relative column offset in FormulaR1C1?
To reference a column relative to the active cell, enclose the offset number in square brackets. For example, RC[-1] refers to the same row but one column to the left, while RC[2] refers to two columns to the right.
Why is my VBA formula evaluating to plain text instead of calculating?
This usually happens if the target cell's number format is set to 'Text'. Ensure you change the cell format to 'General' before applying the macro. Additionally, make sure your VBA script uses the '.FormulaR1C1' property to assign the formula, rather than '.Value' or '.Text'.




