How to add formula to a worksheet range using VBA Codes

How to add formula to a worksheet range using VBA


Adding formulas to ranges is common task when you workaround with Excel VBA.

If you are good in entering formulas in Excel, you can become an expert in adding VBA formulas too.

Eg :

We have some values in a range A1:A20 and We have some values in B1:B20 also. We need to get the difference of all values in B column with corresponding Cell in Column A, in the Column C.

In Excel we will enter a formula = B1-A1 in the cell C1 and do a fill down or a copy paste till C20. It's the simplest formula when we work in Excel.

But How to get the same formulas in C1:C20 using VBA codes ?

You can use two ways.

1. Enter Activesheet.Range("C1:C20").Value = "RC[-1] + RC[-2]" in your code.
2. Enter Activesheet.Range("C1:C20").FormulaR1C1= "RC[-1] + RC[-2]" in your code.

Both codes with do the same task. Note, VBA is using R1C1 reference in entering the formulas to Excel. It's better to Get a basic idea about how R1C1 reference style works, before you start working with formulas in VBA.

Solution: If you have any doubt in VBA, first try to record the task which you want to automate and see the codes. Same way, you record the task of entering the above formulas using macro recorder. See the codes.