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