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
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.
- We can use similar approach to apply formula in a single cell.
- To scroll down the formula in a column we can use loop or FillDown property of range object.
Post you may like