To enter a formula using VBA into a single cell, We use Range().Formula property and assign it to worksheet function.
For Example, VBA code to enter VLOOKUP formula in cell B1 is
RANGE("B1").FORMULA = "=VLOOKUP(A1,SHEET2!A:B,2,0)"
Suppose, We want to apply the same formula from Range B1 through B10, then change the VBA
code to
RANGE("B1:B10").FORMULA = "=VLOOKUP(A1,SHEET2!A:B,2,0)"
Now, in the new code Range A1 is fixed or absolute for cells from B1 through B10. To change it to relative reference, replace A1 with A:A on the RHS of the code. Now, the new code look like
RANGE("B1:B10").FORMULA = "=VLOOKUP(A:A,SHEET2!A:B,2,0)"
Replacing A1 with A:A changes the lookup value from Absolute to Relative reference (i.e. for cell B1, A:A is A1; for B2, A:A is A2 and so on..).
NOTE:
Use Coupon Code 580EDUNF83 to get additional 20% discount
This relative referencing style doesnot work for EOMONTH worksheet function. The syntax for EOMONTH is EOMONTH(START DATE, MONTHS).
Let RANGE("B1:B10").FORMULA = "=EOMONTH(A1,0)"
In the above formula, If we replace A1 with A:A then it will return #VALUE error. An alternative to get the relative reference to such worksheet functions is replacing A1 with the below formula INDIRECT(ADDRESS(ROW(),1,4)).
So the correct code would be
RANGE("B1:B10").FORMULA = "=EOMONTH(INDIRECT(ADDRESS(ROW(),1,4)),0)"