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 https://stackoverflow.com/questions/45321109/vba-variable-in-formula and https://stackoverflow.com/questions/54400248/variables-in-formula — AJD, Sep 02 '19 at 20:03
Possible duplicate of [VBA -- variable in .formula](https://stackoverflow.com/questions/45321109/vba-variable-in-formula) — AJD, Sep 02 '19 at 20:03

Tom Sharpe · Accepted Answer · 2019-09-02T19:39:29.123

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"")"

edited Sep 02 '19 at 19:39

answered Sep 02 '19 at 19:33

Tom Sharpe

1 Answers1