Thread: Evaluate formula via VBA

Are you trying to insert the formula into the cell, or only put the result of the formula into the cell?

If the former you need to use the .Formula property of the range and the formula will have to be enclosed in quotes.

If the latter then you can't just quote the formula, you'll have to translate it into VBA first.

Working with a simpler formula - where you're inserting =B2+C2 into each row in A from 2 until the end of the sheet the two options would be:

Sheets("LC").Range("A" & x).Formula="=SUM(B" & x & "+C" & x &")"

Or

Sheets("LC").Range("A" & x).Value = WorksheetFunction.Sum(Sheets("LC").Range("B" & x & ":C" & x))

Hope that helps.

I want to insert the result of the formula.
This is a simpler version but I have i have mismatch error:
For X = 1 To 10
P = Evaluate("=(SUMPRODUCT(--(spc=H" & X & "),--(mdate<=IF(K" & X & "=""," * ",K" & X & ")),--(msale)))")
MsgBox P
Next X
End Sub

Could you use code tags, please?

I think the problem is with your quotation marks. Try:

P = Evaluate("=(SUMPRODUCT(--(spc=H" & X & "),--(mdate<=IF(K" & X & ""="","" * "",K" & X & ")),--(msale)))")