How to Use FormulaR1C1 Absolute Reference in Excel VBA

While working in Excel, you might hear the absolute cell reference. This type of 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. What if you need to use it in R1C1 referencing style? In this article, we’ll show the 6 examples to use FormulaR1C1 absolute cell reference in Excel VBA with the necessary explanation.


Download Practice Workbook


Basics of FormulaR1C1 Absolute Reference


What Does FormulaR1C1 Mean in 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 R5C2 instead of using a B5 cell.


What is R1C1 Absolute Reference?

Having lots of flexibility, the R1C1 referencing style provides both absolute and relative references. Often we use the absolute reference when we want to fix a certain cell value. 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.


Examples of Using FormulaR1C1 Absolute Reference in Excel VBA

Now, we’ll show some fundamental examples first. Later, you’ll see some real-life examples of using the FormulaR1C1 to understand the property clearly. Prior to doing that, let’s see the process of inserting a new module in Excel VBA if you don’t know.

➤ Firstly, open a module by clicking the Developer tab > Visual Basic.

How to Insert VBA Code

➤ Secondly, go to Insert > Module.

How to Insert VBA Code


Basic Examples of Using FormulaR1C1 Absolute Reference


1. FormulaR1C1 Absolute Reference to Sum up

If you want to add the values of two or more cells where the values remain unchanged, you may do that easily in R1C1 style using the FormulaR1C1.

excel vba formular1c1 absolute reference to sum up

Now, just copy the following code into the newly created module.

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

excel vba formular1c1 absolute reference to sum up

In the above code, we used the Worksheets application to return the sheet namely Sum. Then we utilized the Range object to represent the cell E5 where we want to get the output. Furthermore, R5C2, R5C3, and R5C4 refer to the value of B5, C5, and D5 respectively.

Next, when you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get the output of 60.

excel vba formular1c1 absolute reference to sum up

Read More: Excel VBA: R1C1 Formula with Variable (3 Examples)


2. FormulaR1C1 Absolute Reference to Multiply

In addition, you may use the FormulaR1C1 absolute reference to multiply. For example,  you want to multiply the inputs located in the C4:C6 cell range.

excel vba formular1c1 absolute reference to multiply

For multiplying the absolute reference, copy the following code.

Sub FormulaR1C1_Absolute_Multiplication()
Worksheets("Multiplication").Range("C7").FormulaR1C1 = "=R4C3*R5C3*R6C3"
End Sub

excel vba formular1c1 absolute reference to multiply

Here, R4C3, R5C3, and R6C3 refer to the value of C4, C5, and C6 respectively.

After running the code, you’ll get the output of the multiplication is 6000.

excel vba formular1c1 absolute reference to multiply

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


Similar Readings


3. Using FormulaR1C1 Absolute Reference to Divide

Moreover, you may divide the value of two cells utilizing the FormulaR1C1. Such as you may calculate the quotient between Input1 and Input2.

absolute reference to divide

So copy the following code to execute the division.

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

absolute reference to divide

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

Within a short time, you’ll get a quotient of 2 after running the code.

absolute reference to divide

Read More: Absolute Cell Reference Shortcut in Excel (4 Useful Examples)


Real-Life Based Examples of Using FormulaR1C1 Absolute Reference


1. Converting One Currency to Another Currency

Let’s say, you have a dataset where costs for expenditure sectors are given in Euro(€) currency. And you need to convert the currency to USD($).

excel vba formular1c1 absolute reference converting one currency to another currency

More importantly, the exchange rate (€ to $) is 1.09 which is in the C11 cells. To get the costs in USD($) currency, we have to multiply the costs in Euro (C5:C9 cell range) and the exchange rate(C11 cell). In the A1 style, the exchange rate will be used as an absolute cell reference and it would look like $C$11.

However, you may accomplish the same task using the FormulaR1C1.

Let’s explore the process.

Just copy the following code into a module.

Sub FormulaR1C1_Absolute_ConvertingCurrency()
Range("D5").FormulaR1C1 = "=R5C3*R11C3"
Range("D6").FormulaR1C1 = "=R6C3*R11C3"
Range("D7").FormulaR1C1 = "=R7C3*R11C3"
Range("D8").FormulaR1C1 = "=R8C3*R11C3"
Range("D9").FormulaR1C1 = "=R9C3*R11C3"
End Sub

excel vba formular1c1 absolute reference converting one currency to another currency

Here, R11C3 represents the absolute cell reference $C$11.

After running the code, you’ll get the costs in USD($) shortly.

excel vba formular1c1 absolute reference converting one currency to another currency


2. Computing Payment Using FormulaR1C1 Absolute Reference

Likewise, you may compute payment for employees utilizing the FormulaR1C1 property.

In the following dataset, working hours are given for each employee (C5:C9 cell range). More significantly, the hourly wage rate is 15.20 (C11 cell). Needless to say, we have to use the C11 as an absolute reference ($C$11 or R11C3).

computing payment in R1C1 style

Now, copy the following code.

Sub FormulaR1C1_Absolute_ComputingPayment()
Range("D5").FormulaR1C1 = "=R5C3*R11C3"
Range("D6").FormulaR1C1 = "=R6C3*R11C3"
Range("D7").FormulaR1C1 = "=R7C3*R11C3"
Range("D8").FormulaR1C1 = "=R8C3*R11C3"
Range("D9").FormulaR1C1 = "=R9C3*R11C3"
End Sub

computing payment in R1C1 style

You’ll get the payment as shown in the below screenshot if you run the above code.

computing payment in R1C1 style


3. Calculating Amount with a Tax Rate

Lastly, you may determine the tax amount for a certain amount if the tax rate is given.

For example, the below picture shows the sales (D5:D9 cell range) of some items. Besides, the tax rate is in (D11 cell). And you need to calculate the sales tax for each item.

calculating amount with a tax rate

To carry out the calculation, copy the code into a module.

Sub FormulaR1C1_Absolute_Calculating_with_Tax()
Range("E5").FormulaR1C1 = "=R5C4*R11C4"
Range("E6").FormulaR1C1 = "=R6C4*R11C4"
Range("E7").FormulaR1C1 = "=R7C4*R11C4"
Range("E8").FormulaR1C1 = "=R8C4*R11C4"
Range("E9").FormulaR1C1 = "=R9C4*R11C4"
End Sub

calculating amount with a tax rate

Here, the absolute reference ($D$11) is R11C4 in the R1C1 style notation.

Next, run the code and you’ll get the sales tax immediately as represented in the below picture.

calculating amount with a tax rate


Things to Remember

  • In the case of the absolute reference, you might use R5C2 simply or something like that. But you have to insert the row and column number with square brackets [ ] in the case of relative reference. So, the R5C2 will be R[4]C[2] or such stuff.
  • Besides, the reference might be R[4]C2 in the case of mixed reference.

Conclusion

This is how you might use FormulaR1C1 absolute reference in Excel VBA. We strongly believe, here you found something that you wouldn’t get anywhere. Anyway, if you have any queries or recommendations, please share them in the comments section.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo