Insert Formula using VBA Excel | debugvba.com

insert formula in vba excel

Formula is a property of Range object. It is used to apply a formula in a range. With this property ,we can apply an inbuilt excel formula or a user defined expression. We shall see both in the examples below.

 

Applying inbuilt excel formula

We can apply an inbuilt excel formula in a cell, like sum, count, max, min, vlookup etc. In the below shown example we have applied a sum formula in cell D1.

 

Sub RangeFormula_v1()

Range("D1"). Formula = "=sum (A1:B3)"

End Sub

 

insert formula in vba excel

 

 

If we look at the image above, we can see that we had data in range A1:B3. Now, we have applied the sum formula in cell D1 to add data in all the cells in range A1:B3.

 

Applying user defined expressions

We can apply the user defined expression in a cell with Formula property. Example of user defined expression can be A1+ B1 or A1*10 + B1*15 etc.

 

Sub RangeFormula_v2()

Range("E1"). Formula = "=A1+B1"

End Sub

 

In the code mentioned above we are adding the values in cell A1 and B1. We shall apply this formula in cell E1. In the below image we can see that formula (A1+B1) is applied in cell E1.

 

insert formula in vba excel

 

Note

  1. We can  use similar approach to apply formula in a single cell.
  2. To scroll down the formula in a column  we can use loop or FillDown property of range object.


Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula

Post you may like

Copy Folders , Subfolder in VBA Excel