Expected: end of statement (VBA) by Sheets("PrepaymentCustomer").Range("B2:B4")

Question

I am having a Problem with VBA when writing the following line:

ActiveCell.FormulaR1C1 = _
        "=IF(COUNTIF(Sheets("PrepaymentCustomer").Range("B2:B4"),RC[-38])>=1,""yes"",""no"")"

VBA states an 'Compile error: Expected: end of statement'. Earlier in the code the Sheet "Overview" is selected and then the Cell with

Sheets("Overview").Select
Range("AO2").Select

Can anyone help me why this is not working? I figured the error is caused by the statement Sheets("PrepaymentCustomer").Range("B2:B4").

Thanks

You're conflating VBA and formula syntax. – BigBen Nov 16 '20 at 15:29 — BigBen, Nov 16 '20 at 15:29

score 1 · Accepted Answer · answered Nov 16 '20 at 15:27

1

Your double quotes are closing your string. Do you need the Sheets prefix at all in a formula, PaymentCustomer!B2:b4

so "=IF(COUNTIF(Sheets(" is what you'll get in the string. Like your Yes & No's is the way it will need to be.

answered Nov 16 '20 at 15:27

Nathan_Sav

Oh damn. Thank you very much, that solved my issue here. – Simon Nov 16 '20 at 15:29

1 Answers1