Excel VBA: R1C1 Formula with Variable (3 Examples)

While working in Excel, you might hear the absolute and relative cell references. We can use both types of reference in the R1C1 formula with variables in Excel VBA. The absolute cell reference remains unchanged if you copy, move or even use it in an array. Besides, you can use the absolute reference easily in the A1 reference style. The relative cell reference may be changed if you copy, move or even use it in an array What if you need to use it in the R1C1 referencing style? Today, in this article, We will learn three quick and suitable ways how Excel VBA R1C1 Formula with variable works, to Sum Up, Multiplication, and Division with appropriate illustrations.

Introduction to the R1C1 Formula in Excel VBA

Macro uses FormulaR1C1 property which returns the formula in R1C1 style annotation. In fact, R1C1 is the opposite of the A1 referencing style that we are accustomed to. However, R1C1 simply depicts row 1 and column 1.

That means you may change the row and column number whenever you want and it will work the same as the usual A1 style annotation. For example, you may use R4C3 instead of using a C4 cell. Having lots of flexibility, the R1C1 referencing style provides both absolute and relative references.

R1C1 Formula with Absolute Reference

Certainly, you may use the absolute reference in R1C1 style if you want. In that case, you don’t need to use the dollar sign ($). For example, the absolute reference of the $B$5 cell would be R5C2 in R1C1 style.

R1C1 Formula with Relative Reference

On the other hand, you may use the relative reference in R1C1 style if you want. For example, the relative reference of the D5 cell would be R[4]C[3] in R1C1 style.

How to Use R1C1 Formula with Variable in Excel VBA: 3 Suitable Ways

1. Apply R1C1 Formula with Variable to Sum Up in Excel VBA

Now I’ll show how to sum up by using the R1C1 formula with variables in a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will sum up the cells from R5C2 to R5C4. Let’s follow the instructions below, to sum up by using the R1C1 formula with variables in Excel VBA!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – FormulaR1c1 with Variable will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the FormulaR1c1 with Variable module pops up. In the FormulaR1c1 with Variable module, write down the below VBA

Sub R1C1_Formula_with_Variable_Sum()
Worksheets("Sum").Range("E5").FormulaR1C1 = "=R5C2+R5C3+R5C4"
End Sub

• Furthermore, R5C2, R5C3, and R5C4 refer to the value of B5, C5, and D5
• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

• After running the VBA Code, you will be able to get 120 as the output of the R1C1 formula with variables that have been given in the below screenshot.

Read More: How to Hard Code in Excel

2. Perform Multiplication Using R1C1 Formula with Variable in Excel VBA

In this method, we will use the R1C1 formula to multiply. This is an easy and time-saving way also. Let’s follow the instructions below to use the R1C1 formula to multiply!

Step 1:

• First, according to method 1, insert a new module and type the below VBA code to multiply the cells. The VBA code is,

Sub R1C1_Formula_Multiplication()
Worksheets("Multiplication").Range("E5").FormulaR1C1 = "=R5C2*R5C3*R5C4"
End Sub

• Here, R5C2, R5C3, and R5C4 refer to the value of B5, C5, and D5 respectively.

• Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• While running the VBA Code, you will be able to get 60000 as the output of the R1C1 formula that has been given in the below screenshot.

Read More: How to Keep a Cell Fixed in Excel Formula 

3. Use R1C1 Formula with Variable to Divide in Excel VBA

Last but not the least, we will use the R1C1 formula to divide. This is an easy and time-saving way also. Moreover, you may divide the value of two cells utilizing the R1C1 Formula. Such as you may calculate the quotient between Input1 and Input2.

Let’s follow the instructions below to use the R1C1 formula to divide!

Step 1:

• First, insert a new module according to method 1, and type the below VBA code to calculate the quotient between Input1 and Input2. The VBA code is,

Sub R1C1_Formula_Division()
Worksheets("Division").Range("D5").FormulaR1C1 = "=R5C2/R5C3"
End Sub

• Here, R5C2 and R5C3 represent the value of B5 and C5 cells respectively.

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• As a result, you will be able to get 2 as the output of the R1C1 formula that has been given in the below screenshot.

Read More: How to Make Different Types of Cell Reference in Excel

Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

I hope all of the suitable methods mentioned above to use R1C1 formula with variables in VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• Cell Reference in Excel VBA
• Relative and Absolute Cell Address in the Spreadsheet
• Difference Between Absolute and Relative Reference in Excel
• How to Use Cell References in Excel Formula 

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel