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.


Download Practice Workbook

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


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.


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

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

Apply R1C1 Formula with Variable to Sum Up in Excel VBA

  • 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

Apply R1C1 Formula with Variable to Sum Up in Excel VBA

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

Apply R1C1 Formula with Variable to Sum Up in Excel VBA

Read More: How to Use FormulaR1C1 Absolute Reference in Excel VBA


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.

Perform Multiplication Using R1C1 Formula with Variable in Excel VBA

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

Perform Multiplication Using R1C1 Formula with Variable in Excel VBA

Read More: How to Use Cell Value as Worksheet Name in Formula Reference in Excel


Similar Readings


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.

Use R1C1 Formula with Variable to Divide in Excel VBA

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

Use R1C1 Formula with Variable to Divide in Excel VBA

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.

Use R1C1 Formula with Variable to Divide in Excel VBA

Read More: Excel VBA Examples with Cell Reference by Row and Column Number


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


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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

1 Comment
  1. Excellent tutorial.
    R1C1 notation works well if you know the cell location.
    I have a column of valid dates followed by a column of data. I want another column to show only the date (yyyy-mm), and another column to show the average per month.
    I am looping through the data using a subscript. I understand that R1C1 does not support subscripts.
    How can I extract only yyyy-mm from the date column when the date column changes month using a subscript?
    Thank you.

Leave a reply

ExcelDemy
Logo