Trouble using formula in Excel VBA

[dougdrex]

I'm using Excel 2010 VBA. Here is the issue I'm facing.

I would like to insert a formula into cell Q2 and copy it down to the rest of the rows in that column (number of rows will vary). Here is the code I have, but I keep getting a syntax error. I don't why for sure, but I suspect it has to do with the "{" and "}" symbols in the formula. Can anyone confirm and suggest a workaround?

Range("Q2).Formula = "=SUM(COUNTIF(P2,{"Account*No Debits",_    "Account*No Credits","Account*Dormant","Whereabouts Unknown",_
    "Cheque*Used","Cheque*not Found","Account*Blocked","Account*Frozen",_
    "Payment involving crisis related customer or institution. Do you want to proceed ?", _
    "Cheque missing or Account mismatch"}))>0"

 

[steve the fish]

Instead of:

"Account*No Debits"
you need:

""Account*No Debits""

and so on.

 

[dougdrex]

Instead of:

"Account*No Debits"
you need:

""Account*No Debits""

and so on.

I tried that solution, but still got Syntax error. Is there something special I should do regarding the "{" and "}" symbols in the formula?

 

[steve the fish]

You had me thinking i was going mad for a minute there. You missed the quote after Q2... Anyway try like this:

Range("Q2").Formula = "=SUM(COUNTIF(P2,{""Account*No Debits"",""Account*No Credits"",""Account*Dormant""," & _
    """Whereabouts Unknown"",""Cheque*Used"",""Cheque*not Found"",""Account*Blocked""," & _
    """Account*Frozen"",""Payment involving crisis related customer or institution. Do you want to proceed ?""," & _
    """Cheque missing or Account mismatch""}))>0"

 

[dougdrex]

You had me thinking i was going mad for a minute there. You missed the quote after Q2... Anyway try like this:

Range("Q2").Formula = "=SUM(COUNTIF(P2,{""Account*No Debits"",""Account*No Credits"",""Account*Dormant""," & _
    """Whereabouts Unknown"",""Cheque*Used"",""Cheque*not Found"",""Account*Blocked""," & _
    """Account*Frozen"",""Payment involving crisis related customer or institution. Do you want to proceed ?""," & _
    """Cheque missing or Account mismatch""}))>0"

Thank you, that worked perfectly!

I can't believe I missed the quote mark after Q2! It's the smallest of details that usually causes the biggest of problems.