While working in Excel, you might hear the absolute and relative cell references. We can use both types of reference in the R1C1 formula with variables in Excel VBA. The absolute 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. The relative cell reference may be changed if you copy, move or even use it in an array What if you need to use it in the R1C1 referencing style? Today, in this article, We will learn three quick and suitable ways how Excel VBA R1C1 Formula with variable works, to Sum Up, Multiplication, and Division with appropriate illustrations.

**Table of Contents**hide

## Introduction to the R1C1 Formula in Excel 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 **R4C3** instead of using a **C4 **cell. Having lots of flexibility, the **R1C1 **referencing style provides both absolute and relative references.

**R1C1 Formula with Absolute Reference**

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.

**R1C1 Formula with Relative Reference**

On the other hand, you may use the relative reference in **R1C1 **style if you want. For example, the relative reference of the **D5 **cell would be **R[4]C[3]** in **R1C1 **style.

## How to Use R1C1 Formula with Variable in Excel VBA: 3 Suitable Ways

### 1. Apply R1C1 Formula with Variable to Sum Up in Excel VBA

Now Iâ€™ll show how to sum up by using the **R1C1** formula with variables in a simple **VBA **code. Itâ€™s very helpful for some particular moments**.** From our dataset, we will sum up the cells from **R5C2 **to **R5C4**. Letâ€™s follow the instructions below, to sum up by using the **R1C1 **formula with variables in Excel VBA!

**Step 1:**

- First of all, open a Module, to do that, firstly, from your
**Developer**tab, go to,

**Developer â†’ Visual Basic**

- After clicking on the
**Visual Basic**ribbon, a window named**Microsoft Visual Basic for Applications â€“ FormulaR1c1 with Variable**will instantly appear in front of you. From that window, we will insert a module for applying our**VBA code**. To do that, go to,

**Insert â†’ Module**

**Step 2:**

- Hence, the
**FormulaR1c1 with Variable**module pops up. In the**FormulaR1c1 with Variable**module, write down the below**VBA**

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

- Furthermore,
**R5C2**,**R5C3**, and**R5C4**refer to the value of**B5**,**C5**, and**D5** - Hence, run the
**VBA**To do that, go to,

**Run â†’ Run Sub/UserForm**

- After running the
**VBA Code**, you will be able to get**120**as the output of the**R1C1**formula with variables that have been given in the below screenshot.

**Read More:** How to Hard Code in Excel

### 2. Perform Multiplication Using R1C1 Formula with Variable in Excel VBA

In this method, we will use the **R1C1 **formula to multiply. This is an easy and time-saving way also. Letâ€™s follow the instructions below to use the **R1C1 **formula to multiply!

**Step 1:**

- First, according to
**method 1,****insert**a new module and type the below**VBA**code to multiply the cells. The**VBA code**is,

```
Sub R1C1_Formula_Multiplication()
Worksheets("Multiplication").Range("E5").FormulaR1C1 = "=R5C2*R5C3*R5C4"
End Sub
```

- Here,
**R5C2**,**R5C3**, and**R5C4**refer to the value of**B5**,**C5**, and**D5**respectively.

- Further, run the
**VBA**To do that, go to,

**Run â†’ Run Sub/UserForm**

**Step 2:**

- While running the
**VBA Code**, you will be able to get**60000**as the output of the**R1C1**formula that has been given in the below screenshot.

**Read More: **How to Keep a Cell Fixed in Excel FormulaÂ

### 3. Use R1C1 Formula with Variable to Divide in Excel VBA

Last but not the least, we will use the **R1C1** formula to divide. This is an easy and time-saving way also. Moreover, you may divide the value of two cells utilizing the **R1C1** Formula. Such as you may calculate the quotient between **Input1 **and **Input2**.

Letâ€™s follow the instructions below to use the **R1C1 **formula to divide!

**Step 1:**

- First,
**insert**a new module according to**method 1,**and type the below**VBA**code to calculate the quotient between**Input1**and**Input2**. The**VBA code**is,

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

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

- Hence, run the VBA To do that, go to,

**Run â†’ Run Sub/UserForm**

**Step 2:**

- As a result, you will be able to get
**2**as the output of the**R1C1**formula that has been given in the below screenshot.

**Read More:** How to Make Different Types of Cell Reference in Excel

## Things to Remember

ðŸ‘‰ You can pop up **Microsoft Visual Basic for Applications **window by pressing **Alt + F11** **simultaneously**.

ðŸ‘‰ If a **Developer **tab is not visible in your ribbon, you can make it visible. To do that, go to,

**File â†’ Option â†’ Customize Ribbon**

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

I hope all of the suitable methods mentioned above to use R1C1 formula with variables in VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Excellent tutorial.

R1C1 notation works well if you know the cell location.

I have a column of valid dates followed by a column of data. I want another column to show only the date (yyyy-mm), and another column to show the average per month.

I am looping through the data using a subscript. I understand that R1C1 does not support subscripts.

How can I extract only yyyy-mm from the date column when the date column changes month using a subscript?

Thank you.