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