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.
➤ Secondly, go to Insert > Module.
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.
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
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.
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.
For multiplying the absolute reference, copy the following code.
Sub FormulaR1C1_Absolute_Multiplication()
Worksheets("Multiplication").Range("C7").FormulaR1C1 = "=R4C3*R5C3*R6C3"
End Sub
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.
Read More: How to Use FormulaR1C1 Absolute Reference in Excel VBA
Similar Readings
- How to Use Variable Row Number as Cell Reference in Excel
- Excel VBA: Cell Reference in Another Sheet (4 Methods)
- How to Find and Replace Cell Reference in Excel Formula
- Excel VBA Examples with Cell Reference by Row and Column Number
- How to Reference Text in Another Cell in Excel (14 Ways)
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.
So copy the following code to execute the division.
Sub FormulaR1C1_Absolute_Division()
Worksheets("Division").Range("D5").FormulaR1C1 = "=R5C2/R5C3"
End Sub
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.
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($).
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
Here, R11C3 represents the absolute cell reference $C$11.
After running the code, you’ll get the costs in USD($) shortly.
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).
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
You’ll get the payment as shown in the below screenshot if you run the above code.
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.
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
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.
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
- Example of Relative Cell Reference in Excel (3 Criteria)
- Relative and Absolute Cell Address in the Spreadsheet
- How to Reference Cell by Row and Column Number in Excel (4 Methods)
- Excel VBA: Get Cell Value from Another Workbook without Opening
- [Fixed!] Relative Cell Reference Not Working in Excel
- How to Use Cell Value as Worksheet Name in Formula Reference in Excel
