Table of Contents
The FormulaR1C1 notation is a method of referencing cells in Microsoft Excel using the Visual Basic for Applications (VBA) programming language. This notation allows users to dynamically refer to cells and ranges in a spreadsheet, making it easier to write and maintain VBA code. In order to use the FormulaR1C1 notation, users must first enable it in the VBA editor. Once enabled, the notation can be used in various VBA functions and statements, such as the Range and Cells functions, to manipulate data and perform calculations in a spreadsheet. Some common examples of implementing the FormulaR1C1 notation in VBA include creating dynamic formulas, looping through cells, and copying and pasting data between sheets. This notation is particularly useful for automating tasks and enhancing the efficiency of data analysis and manipulation in Excel.
Use FormulaR1C1 in VBA (With Examples)
You can use the FormulaR1C1 property in VBA to make an absolute reference or a relative reference to a particular cell in a sheet.
There are two common ways to use this property:
Method 1: Use FormulaR1C1 to Make Absolute Reference
Sub MultipyCell()
Range("C5").FormulaR1C1 = "=R1C1*20"
End Sub
When you run this particular macro, cell C5 will display the result of the cell in row 1 and column 1 multiplied by 20.
Method 2: Use FormulaR1C1 to Make Relative Reference
Sub MultipyCell()
Range("C5").FormulaR1C1 = "=R[-4]C[-2]*20"
End Sub
When you run this particular macro, cell C5 will display the result of the cell that is 4 rows above it and 2 columns to the left of it multiplied by 20.
The following examples show how to use each method in practice with a sheet in Excel that contains the value 10 in cell A1:
Example 1: Use FormulaR1C1 to Make Absolute Reference
We can create the following macro to multiply the value of the cell in row 1 and column 1 by 20 and display the results in cell C5:
Sub MultipyCell()
Range("C5").FormulaR1C1 = "=R1C1*20"
End Sub
When we run this macro, we receive the following output:
We can see that Excel used the formula =$A$1*20 to calculate the result in cell C5.
Since we used R1C1 in our formula in VBA, we made an absolute reference to the cell in the first row and the first column, which is cell A1.
Example 2: Use FormulaR1C1 to Make Relative Reference
We can create the following macro to multiply the value of the cell that is 4 rows above and 2 columns to the left of cell C5 by 20 and display the results in cell C5:
Sub MultipyCell()
Range("C5").FormulaR1C1 = "=R[-4]C[-2]*20"
End Sub
When we run this macro, we receive the following output:
We can see that Excel used the formula =A1*20 to calculate the result in cell C5.
Since we used brackets with R[-4]C[-2] in our formula in VBA, we made a relative reference to the cell that is 4 rows above and 2 columns to the left of cell C5, which is cell A1.
Note: You can find the complete documentation for the VBA FormulaR1C1 property .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: