Excel VBA: Insert Formula with Relative Reference (All Possible Ways)

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

VBA Code to Insert Formula with Relative Reference in Excel


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.

Data Set to Insert Formula with Relative Cell Reference in Excel VBA

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

VBA Code to Insert Formula with Relative Reference in Excel

⧭ 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

VBA Code to Insert Formula with Relative Cell Reference in Excel VBA

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

Output to Insert Formula with Relative Cell Reference in Excel VBA

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


Similar Readings


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

VBA Code to Insert Formula with Relative Cell Reference in Excel VBA

⧭ 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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo