How to include variables in Range.Formula in VBA?

Question

I am tying to replicate an excel formula IF(A2>300,"PASS","FAIL") using Range.Formula in VBA, but it is not working as required.

I am trying to call variables into the formula itself as shown below but it is retuing "end of statement" error. Somebody please help! Thanks

col_label = "A"
Range("B1").Formula = "=IF(" & col_label & 2" > 300, ""PASS"", ""FAIL"")"

Possible duplicate of stackoverflow.com/questions/45321109/vba-variable-in-formula and stackoverflow.com/questions/54400248/variables-in-formula — AJD
– AJD, Commented Sep 2, 2019 at 20:03

Tom Sharpe · Accepted Answer · 2019-09-02 19:39:29Z

1

Everything after col_label should be in quotes (double-quoted where necessary):

col_label = "A"
Range("B1").Formula = "=IF(" & col_label & "2 > 300, ""PASS"", ""FAIL"")"

answered Sep 2, 2019 at 19:33

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you - OP is referred to screen shot.