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