What Is VBA formulaR1C1?
VBA formulaR1C1 refers to a range property object in Excel that enables you to set or return the formula for the object by using R1C1-style notation. R refers to rows whereas C stands for columns. R1C1 notation references a cell based on the row and column numbers as opposed to a cell reference. For example: R1C1 references to a cell in Row 1 and Column 1, similarly, R2C1 will reference to a cell in Row 2 and Column 1 and so on. R1C1-style notation is a useful tool that allows you to create dynamic formulas for your task automation.
Let us now look at different types of R1C1 references that you can use in VBA.
Key Takeaways
- VBA formulaR1C1 enables you to set or return the formula for the object by using R1C1-style notation.
- There are three different types of R1C1 references, i.e., Absolute references, relative references, and mixed references.
- By using different combinations of R1C1 references, you can lock down rows and columns in your VBA script.
- You can use variable R1C1 references to create dynamic formulas to adjust data changes.
- Ensure that while using R1C1 references, you provide the accurate formula; otherwise, a #REF! error may be displayed while executing the script.
- You can convert A1 formulas to R1C1 in VBA using Application.ConvertFormula method.
Types of R1C1 Reference
There are 4 different types of R1C1 references in VBA as outlined below. These include
- Absolute References
- Relative References
- Mixed References
- Lock Rows and Lock Columns with R1C1
Let’s understand each of these methods to apply in your VBA script. As a prerequisite, you can follow the below steps to enable VBA in Excel.
Open your data source in Excel. Navigate to File – Options – Customize Ribbon and check the Developer option to enable VBA tools in Excel.
.

#1 Absolute References
Absolute references refer to a fixed row and column. These references don’t change when you copy a formula in the current cell to a new cell. For example, R1C1 notation will always refer to the 1st cell of your Excel workbook, i.e., row number 1 and column number 1 when you copy a formula to any other cell.
#2 Relative References
Relative References refer to the position of a cell, and hence, when you copy a formula applicable to a cell, it adjusts based on the position of the cell containing the formula. To specify relative references in your VBA script, you must denote them in a square bracket, as shown below. For example, =R[1]C[1] will instruct the VBA to refer to a cell 1 row down and 1 column next to the current cell.
#3 Mixed References
Mixed reference is a combination of Absolute and Relative references. It means you can either fix both row and column or fix either of them while keeping the other one relative reference.
For example, R[1]C1 will lock the column, keeping the row relative, i.e., it will refer to a cell that is one row down and in the first column. Similarly, R1C[1] will lock the row, keeping the column relative.
#4 Rows and Lock Columns with R1C1
You can also lock columns or rows with R1C1 using one of the 3 references mentioned above. To lock the row, you can use absolute reference and column as relative reference and vice versa. For example, R[1]C1 will lock the column, whereas R1C[1] will lock the row.
Variable R1C1 References
Variable R1C1 references can be used in VBA to create dynamic formulas for your automation apps. They allow you to reference cells on a run-time basis, depending on other cells’ position. A sample variable R1C1 references is provided below:
‘The below code snippet performs a lookup data table.
Sub PerformLookupDataTable()
Dim lookupRange As Range
Set lookupRange = Range(“A2:B10”)
Range(“D2”).FormulaR1C1 = “=VLOOKUP(R[-1]C[-3],R[1]C[-2]:R[9]C[-1],2,FALSE)”
End Sub
Note that you can also use the VBA formulaR1C1 sum where you can perform sum values in a specified range on the same worksheet.
Similarly, you can also perform VBA formulaR1C1 vlookup another worksheet by using the VLOOKUP method.
Examples
In this section, we will go through different examples to demonstrate the VBA formulaR1C1.
Example #1 – VBA formulaR1C1 with absolute references
It is a simple example of creating a VBA formulaR1C1 with absolute references using an Excel file.
To demo this example, follow the steps outlined below:
Step 1: Create a new Excel workbook VBA FormulaR1C1 Demo.xlsx in your local folder, as displayed below.

The VBA FormulaR1C1 Demo.xlsx workbook is a blank Excel workbook.

Step 2: To open the VBA editor in the Excel workbook, click on Developer à Visual Basic. Alternatively, you can also hit Alt+F11 to open the VB editor.

It will open the VBA screen with project hierarchy on the left navigation, as shown below.

Step 3: Under VBAProject, right-click on the Module and choose Insert à Module option from the context menu.

Step 4: This will open a blank VB editor screen, as shown below.

Click on the Run icon in the toolbar. In the Macros screen that opens, specify a name and click on Create.
We have created a macro named R1C1_Absolute_References.

Step 5: In the VBA editor, specify the logic to add the value of the cell in A1 by 100 and display the result in the C5 cell.

For this demo, we have defined the below VBA code.
For clarity, we have included comments so users can understand the code logic.
Sub R1C1_Absolute_References()
‘Add the value of cell in row 1 and column 1 by 100
Range(“C5”).FormulaR1C1 = “=R1C1+100”
End Sub
Once you have specified the code changes, click on save changes.
Step 6: Now click on the Run icon in the toolbar.
We have added debug points to understand the outcome at each step.
Click on the Run icon in the Macros box.

As you can see, post-run, the VBA formulaR1C1 added 100 to the value in A1 and displayed the output, 130 in the C5 cell.

Example #2 – VBA formulaR1C1 with relative references
In this example, we will demonstrate another example of the R1C1 reference type, i.e., Relative Reference.
To demo this example, we will leverage the same Excel workbook VBA FormulaR1C1 Demo.xlsx for this purpose.
Let’s look at the step-by-step processes to apply the VBA script for this demo:
Step 1: Open the VB editor by pressing the Alt+F11 keys on your Windows machine.
Step 2: On the VBAProject hierarchy screen, right-click on the Modules under VBAProject. Choose Insert à Module.

Step 3: Click on the Run icon in the icon toolbar to create a Macro. Provide a meaningful name and click on Create.
We have named the macro as R1C1_Relative_References.

Step 4: In the VBA editor, specify the logic to use relative references, as shown below.

For this demo purpose, we have specified the below VBA code changes
Sub R1C1_Mixed_References()
‘Keep Row fixed column relative
Range(“G7”).FormulaR1C1 = “=R[-3]C4+90”
‘Keep Column fixed Row relative
Range(“H7”).FormulaR1C1 = “=R3C[-4]+45”
End Sub
Step 5: Now run the macro to see the behavior of the code you have specified in the VBA editor. To demonstrate the effect, we have applied debug points at each step.

Click on the Run button to execute the VBA code snippet.

After the execution of the VB code, you will see the output of 200 displayed in cell E5.
Our VB script has added 200 to the cell six rows above and 3 columns left of cell E5 and displayed the output in cell E5.

Example #3 – VBA formulaR1C1 with mixed references
In this demo, we will apply the VBA formula R1C1 with mixed references. We will demonstrate how to lock a row while keeping the column relative and vice versa. We will use the same Excel workbook as the previous examples. To demo the VBA formulaR1C1 with mixed references, follow the steps outlined below:
Step 1: Open a VBA editor by navigating Developer à Visual Basic in a workbook.
Step 2: In the source workbook, navigate to the VBA screen. Right-click on the Modules and choose Insert – Module.

It will open a new macro window, as shown below. Click on the Sub Sub/UserForm run icon available in the toolbar.
Here, we have provided the Macro Name as R1C1_Mixed_References in the Macros screen, as shown below.

Step 3: Specify the logic of your VBA script as shown below. The VBA script performs two operations.
- Adds 90 to the cell 3 rows above the cell G7 and column 4.
- Adds 45 to the row 3 and 4 columns above the cell H7.

We have added the below VBA code for this illustration. Additionally, we have provided comments at each step to highlight the behavior of the step.
Sub R1C1_Mixed_References()
‘Keep Row fixed column relative
Range(“G7”).FormulaR1C1 = “=R[-3]C4+90”
‘Keep Column fixed Row relative
Range(“H7”).FormulaR1C1 = “=R3C[-4]+45”
End Sub
Step 4: To evaluate the outcomes at each step, add debug points, as shown below. Click on the Run icon in the toolbar.

After running the script, VBA will execute the steps as specified in the editor. You will notice the changes to the Excel, as shown below.
You can see that the 90 value is now displayed in cell G7.

Now, run the next step. You will see the debugger in the code with a relative reference to the row.

Once the step is executed, a value of 45 will be assigned to cell H7.

Important Things to Note
- VBA formulaR1C1 can produce errors if the syntax is incorrect. Ensure that error handling is appropriately performed for any reference error scenarios.
- Ensure that the row and column references are accurately specified in the VBA script.
- While you can switch between A1 formulas and R1C1 in Excel, only one style can be used consistently for clarity in VBA.
Frequently Asked Questions (FAQs)
VBA formulaR1C1 is used to return the formula for the object. It is done using the R1C1-style notation in the macro.
VBA formula2R1C1 is primarily used to handle dynamic arrays allowing you to return different outputs with a single formula.
If you use an incorrect R1C1 formula, VBA script execution may return a reference error, i.e., #REF! To handle such errors, you may apply error handling in your VBA script.
To clear a formula set with formulaR1C1, you can set the cell value with an empty string as shown below:
‘ Clear the formula by assigning empty string to the cell
Range(“A1”).FormulaR1C1 = “”
‘ Alternative you can use ClearContents to clear formulas and values from the range
Range(“A1”).ClearContents
You can convert A1 formulas to R1C1 in VBA using Application.ConvertFormula method. This method will convert cell references between the A1 and R1C1 reference styles, and also between the relative and absolute references.
A sample code snippet is provided below
inputFormula = “=SUM(R07C3:R12C3)”
MsgBox Application.ConvertFormula( _
formula:=inputFormula, _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)
Download Template
This article must help understand VBA formulaR1C1 with its features and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is VBA formulaR1C1. We learn how to VVBA formulaR1C1 using various methods, examples, template. You can learn more from the following articles.
Leave a Reply