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

## Download Practice Workbook

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

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

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

### 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 Use FormulaR1C1 Absolute Reference in Excel VBA**

### 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 Use Cell Value as Worksheet Name in Formula Reference in Excel**

**Similar Readings**

**How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)****How to Use Cell References in Excel Formula (All Possible Ways)****Mixed Cell Reference in Excel (4 Examples)****Absolute Cell Reference in Excel (4 Examples)****Example of Mixed Cell Reference in Excel (3 Types)**

### 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:** **Excel VBA Examples with Cell Reference by Row and Column Number**

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

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

**Related Articles**

**How to Reference Text in Another Cell in Excel (14 Ways)****How to Find and Replace Cell Reference in Excel Formula****[Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)****How to Use Variable Row Number as Cell Reference in Excel****Excel VBA: Insert Formula with Relative Reference (All Possible Ways)****Example of Relative Cell Reference in Excel (3 Criteria)**

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.