VBA & Macro Problems

How to Create a Relative VBA Formula Reference in Excel

Kushani NimanthikaKushani Nimanthika Nov 3, 2026 868 views

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.

How to Create a Relative VBA Formula Reference in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet.

2
Locate the Macro Code

In the Project Explorer panel, double-click the module or worksheet containing your FormulaR1C1 script to view the code.

3
Update Absolute Row References

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.

4
Test the Formula Fill

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.

Use RC Notation in FormulaR1C1 for Relative Row References
Understanding R1C1 Relative Syntax: In R1C1 notation, 'R' without a number means 'current row', and 'C' without a number means 'current column'. To specify an offset from the active cell, use brackets, such as R[1]C[-1] (one row down, one column to the left).
Advanced Macros in WPS

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. 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. 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. 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.
Full compatibility with Microsoft Excel VBA code, Macro structures, and R1C1 formula notation.Lightweight installation with an intuitive, familiar user interface for writing scripts.Highly stable performance when processing and filling formulas across large datasets.Free access to core spreadsheet functionalities and seamless format compatibility (.xlsm, .xlsx).
QA img-9

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