I am trying to enter a formula into a cell which contains variable called var1a? The code that I have is:
Worksheets("EmployeeCosts").Range("B" & var1a).Formula = ""SUM(H5:H""& var1a)
But it enters into an excel worksheet with a mistake.
This question is related to
excel
vba
excel-formula
You aren't building your formula right.
Worksheets("EmployeeCosts").Range("B" & var1a).Formula = "=SUM(H5:H" & var1a & ")"
This does the same as the following lines do:
Dim myFormula As String
myFormula = "=SUM(H5:H"
myFormula = myFormula & var1a
myformula = myformula & ")"
which is what you are trying to do.
Also, you want to have the =
at the beginning of the formala.
I would do it like this:
Worksheets("EmployeeCosts").Range("B" & var1a).Formula = _
Replace("=SUM(H5:H{SOME_VAR})","{SOME_VAR}",var1a)
In case you have some more complex formula it will be handy
Source: Stackoverflow.com