# Excel VBA: Insert Formula with Relative Reference (3 Ways)

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF