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.

**Table of Contents**Expand

## 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**.

âž¤ Next, 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 an output of 60.

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

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

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

**Download Practice Workbook**

## 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 with Absolute Cell Reference in Excel
- Absolute Cell Reference Shortcut in ExcelÂ
- What Is and How to Do Absolute Cell Reference in Excel?
- [Fixed] F4 Not Working in Absolute Cell Reference in Excel

**<< Go Back to Absolute Cell Reference |Â Cell Reference in Excel |Â Excel Formulas | Learn Excel**