In this article, we’ll discuss how to use Excel VBA to insert a formula with relative cell references 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
```

**Using VBA to Insert a Formula with Relative Cell Reference: 3 Possible Ways**

Here we have a dataset with the **Names **of some** Products** and their **Sales** in some given years for a company called Mars Group.

We want to calculate the **Sales Growth** percentage for the year **2016-17** in column **I**.

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 is to insert these formulas with relative cell references using VBA.

**Example 1 – Inserting into a Single Cell**

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

**Step 1 – Declaring the Destination Cell**

We declare the destination cell into which the formula will be entered. Here it’s **I5**.

`Set Cell = Range("I5")`

**Step 2 – Allocating the Formula**

Now we allocate the specified formula, **(E5-D5)/D5.**

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

**Step 3 – Inserting the Formula into the Cell**

Finally, we’ll insert the formula into the cell.

`Cell.Formula = Formula`

The complete VBA code is:

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

**Example 2 – Inserting into a Single Column**

**Step 1 – Declaring the Range of the Destination Column**

We declare the range of the destination column, **I5:I4**.

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

**Step 2 – Allocating the Formula and Inserting It into the First Cell**

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

**Step 3 – Copying the Formula**

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

Now we iterate through a **For loop** to paste the formula to the rest of the cells in the column using the **xlPasteFormulas** property of VBA.

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

**Step 5 (Optional) – Turning Off CutCopyMode**

You can skip this step if you want.

`Application.CutCopyMode = False`

The complete VBA code is:

**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 incremental cell references.

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

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

And so on.

**Example 3 – Inserting 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 declare the destination range, **I5:K14**, first.

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

**Step 2 – Allocating the Formula, Inserting It into First Cell, Copying It, and Pasting It into First Column**

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

We iterate through two more **F****or loops** to paste the formula in 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 turn off the **CutCopyMode** of VBA. Again, you can skip this step if you want.

`Application.CutCopyMode = False`

The complete VBA code is:

**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 in each cell of the range **I5:K14**, with incremental cell references.

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

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

On the next row, cell **I6** will get **(E6-D6)/D6**.

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

And so on.

**Things to Remember**

In this article, we’ve used the **xlPasteFormulas** property, which is one of 12 properties of the **PasteSpecial** method of VBA.

**Download Practice Workbook**

**Related Articles **

**<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel**