# How to 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 .