To insert one or more formula with relative cell reference is one of the most important and widely used features that we encounter everyday while working with **VBA** in Excel. In this article, Iâ€™ll show you how you can use Excel **VBA** to insert formula with relative cell reference in a single cell or a range of cells.

Excel VBA to Insert Formula with Relative Reference (Quick View)

```
Sub Insert_Formula_in_Single_Cell()
Set Cell = Range("I5")
Formula = "=(E5-D5)/D5"
Cell.Formula = Formula
End Sub
```

**Table of Contents**hide

**Download Practice Workbook**

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

**How to Use Excel VBA to Insert Formula with Relative Cell Reference**

Here weâ€™ve got a data set with the **Names of some Products** and their **Sales** in some given years of a company called Mars Group.

We want to have the **Sales Growth** percentage of the year **2016-17** in column **I**. And the formula for **Sales Growth** is:

`=(Sales (Present Year) - Sales (Previous Year) / Sales (Previous Year)`

For example, for the first product **Desktop**, the formula in cell **I5** will be:

`=(E5-D5)/D5`

For the second product **Laptop**, itâ€™ll be:

`=(E6-D6)/D6`

And so on.

Our objective today is to insert these formulas with relative cell references with the help of **VBA**.

**1. Insert Formula with Relative Cell Reference into a Single Cell with Excel VBA**

First of all, letâ€™s learn to insert a formula into a single cell with **VBA**.

For example, letâ€™s insert the formula **(E5-D5)/D5 **into cell **I5**.

I am showing you the step-by-step procedure to accomplish this.

**â§Ş Step 1: Declaring the Destination Cell**

First of all, we have to declare the destination cell into which the formula will be entered. Here itâ€™s **I5**.

`Set Cell = Range("I5")`

**â§Ş Step 2: Allocating the Formula**

Next, weâ€™ve to allocate the specified formula. Here it is **(E5-D5)/D5.**

`Formula = "=(E5-D5)/D5"`

**â§Ş Step 3: Inserting the Formula into the Cell**

Finally, weâ€™ll insert the formula into the cell. This is the most important step.

`Cell.Formula = Formula`

So, the complete **VBA** code will be:

**â§ VBA Code:**

```
Sub Insert_Formula_in_Single_Cell()
Set Cell = Range("I5")
Formula = "=(E5-D5)/D5"
Cell.Formula = Formula
End Sub
```

**â§ Output:**

Run this code. Itâ€™ll enter the formula **(E5-D5)/D5 **into cell **I5**. The output of the formula is **26%**.

**Read More:** **How to Use Cell References in Excel Formula (All Possible Ways)**

**2. Enter Formula with Relative Cell Reference into a Single Column with Excel VBA**

Weâ€™ve learned to enter a formula into a single cell with **VBA**. Now weâ€™ll learn to enter formulas into a single column.

**â§Ş Step 1: Declaring the Range of the Destination Column**

First of all, weâ€™ll declare the range of the destination column. Here itâ€™s **I5:I4**.

`Set Rng = Range("I5:I14")`

**â§Ş Step 2: Allocating the Formula and Inserting It into the First Cell**

Next, weâ€™ll allocate the formula and insert it into the first cell of the column.

```
Formula = "=(E5-D5)/D5"
Rng.Cells(1, 1).Formula = Formula
```

**â§Ş Step 3: Copying the Formula**

Then weâ€™ll copy the formula from the first cell.

`Rng.Cells(1, 1).Copy`

**â§Ş Step 4: Pasting It to the Rest of the Cells with a For-Loop**

This is the most important step. Weâ€™ll iterate through a **for-loop** to paste the formula to the rest cells of the column. Weâ€™ll use the **xlPasteFormulas** property of **VBA** for this purpose.

```
For i = 2 To Rng.Rows.Count
Â Â Â Rng.Cells(i, 1).PasteSpecial Paste:=xlPasteFormulas
Next i
```

**â§Ş Step 5 (Optional): Turning Off the CutCopyMode**

Finally, weâ€™ll turn off the **CutCopyMode** of **VBA**. You can skip this step if you want.

`Application.CutCopyMode = False`

So the complete **VBA** code will be:

**â§ VBA Code:**

```
Sub Insert_Formula_in_Single_Column()
Set Rng = Range("I5:I14")
Formula = "=(E5-D5)/D5"
Rng.Cells(1, 1).Formula = Formula
Rng.Cells(1, 1).Copy
For i = 2 To Rng.Rows.Count
Â Â Rng.Cells(i, 1).PasteSpecial Paste:=xlPasteFormulas
Next i
Application.CutCopyMode = False
End Sub
```

**â§ Output:**

Run this code. Itâ€™ll enter the **Sales Growth **formula into each cell of column **I5:I14** with increasing cell reference.

For example, cell **I5** will get **(E5-D5)/D5**.

Cell **I6** will get **(E6-D6)/D6**.

And so on.

**Read More:** **How to Reference Cell by Row and Column Number in Excel (4 Methods)**

**Similar Readings**

**How to Apply Formula to Entire Column Without Dragging in Excel****Mixed Cell Reference in Excel (4 Examples)****How to Use Point and Click Method in Excel (3 Examples)****Cell Reference in Excel VBA (8 Examples)****How to Apply Same Formula to Multiple Cells in Excel (7 Ways)**

**3. Insert Formula with Relative Cell Reference into Multiple Columns with Excel VBA**

Finally, weâ€™ll learn to insert formulas with relative cell references into multiple columns.

Letâ€™s enter the formulas for the **Sales Growth** percentages for the years **2016-2017**, **2017-2018**, and **2018-2019** into the range **I5:K14** of the worksheet.

**â§Ş Step 1: Declaring the Destination Range**

Same as the earlier two methods, we have to declare the destination range first. Here itâ€™s **I5:K14**.

`Set Rng = Range("I5:K14")`

**â§Ş Step 2: Allocating the Formula, Inserting It into First Cell, Copying It, and Pasting It into First Column**

Then weâ€™ll allocate the formula for the first cell, insert it into the first cell, copy it, and then paste it into the first column. These are pretty similar to method **2**.

```
Formula = "=(E5-D5)/D5"
Rng.Cells(1, 1).Formula = Formula
Rng.Cells(1, 1).Copy
For i = 2 To Rng.Rows.Count
Â Â Rng.Cells(i, 1).PasteSpecial Paste:=xlPasteFormulas
Next i
```

**â§Ş Step 3: Pasting the Formula to the Rest of the Columns**

Next, weâ€™ll iterate through two more **for loops** to paste the formula to the rest of the column.

```
For i = 1 To Rng.Rows.Count
Â Â For j = 2 To Rng.Columns.Count
Â Â Â Â Rng.Cells(i, j).PasteSpecial Paste:=xlPasteFormulas
Â Â Next j
Next i
```

**â§Ş Step 4 (Optional): Turning Off the CutCopyMode**

Finally, weâ€™ll turn off the **CutCopyMode** of **VBA**. Similar to method **2**, you can skip this step if you want.

`Application.CutCopyMode = False`

So the complete **VBA** code will be:

**â§ VBA Code:**

```
Sub Insert_Formula_in_Multiple_Columns()
Set Rng = Range("I5:K14")
Formula = "=(E5-D5)/D5"
Rng.Cells(1, 1).Formula = Formula
Rng.Cells(1, 1).Copy
For i = 2 To Rng.Rows.Count
Â Â Rng.Cells(i, 1).PasteSpecial Paste:=xlPasteFormulas
Next i
For i = 1 To Rng.Rows.Count
Â Â For j = 2 To Rng.Columns.Count
Â Â Â Â Rng.Cells(i, j).PasteSpecial Paste:=xlPasteFormulas
Â Â Next j
Next i
Application.CutCopyMode = False
End Sub
```

**â§ Output:**

Run this code, and itâ€™ll insert the **Sales Growth** formula to each cell of the range **I5:K14**, with increasing cell reference.

For example, cell **I5** will get **(E5-D5)/D5**.

Cell **J5** will get **(F5-E5)/E5**.

Again, cell **I6** will get **(E6-D6)/D6**.

Cell **J6** will get **(F6-E6)/E6**.

And so on.

**Read More: ****How to Insert Formula for Entire Column in Excel (6 Quick Ways)**

**Things to Remember**

In this article, weâ€™ve used the **xlPasteFormulas** property of the **PasteSpecial** method of **VBA**. Besides this property, there are **11** more properties of the **PasteSpecial** method. Click here to know them in detail.

**Conclusion**

So, these are the ways to insert a formula with relative cell reference into a single cell or a range of cells with **VBA** in Excel. Do you have any questions? Feel free to ask us. And donâ€™t forget to visit our site **ExcelDemy** for more posts and updates.

**Related Articles**

**Relative and Absolute Cell Address in the Spreadsheet****How to Apply a Formula to Multiple Sheets in Excel (3 Methods)****Example of Relative Cell Reference in Excel (3 Criteria)****How to Use Multiple Excel Formulas in One Cell (with Easy Steps)****[Fixed!] Relative Cell Reference Not Working in Excel****How to Apply Formula in Excel for Alternate Rows (5 Easy ways)****Excel VBA: Cell Reference in Another Sheet (4 Methods)**