Expected End of Statement in formula with quotes

Question

Sub FillNames()

Range("D2:D56").SpecialCells(xlCellTypeBlanks).Formula = _
    "=IF(AND(C>800,C<900),  "YES", "NO")"

End Sub

I get the compile error expected: end of statement. It seems to have issue with the quotes around the words YES and NO.

If I remove the quotes around YES and NO and run the macro, it will run and print what is in the formula quotes into the cell. I don't understand then how the quotes cause a syntax error.

I am running Excel 2011 for Mac, don't know if that has anything to do with it.

Add `Option Explicit` to the top of the module and let us know what happens. — Smandoli, Jun 11 '14 at 21:56
Sub FillNames() Range("D2:D56").SpecialCells(xlCellTypeBlanks).Formula = "=IF(AND(C>800,C<900), YES, NO)" End Sub — PA_Commons, Jun 11 '14 at 22:00
that will print what is between the "" after the = into the cells, it won't function obviously, because it's not properly formatted. — PA_Commons, Jun 11 '14 at 22:00

score 10 · Accepted Answer · answered Jun 11 '14 at 21:58

10

Double-quotes in strings need to be escaped by doubling them up:

Range("D2:D56").SpecialCells(xlCellTypeBlanks).Formula = _
                    "=IF(AND(C>800,C<900),  ""YES"", ""NO"")"

answered Jun 11 '14 at 21:58

Tim Williams

154,628
8
97
125

Firstly, Thank You. I'm sure that was simple for some. – PA_Commons Jun 11 '14 at 22:13
1

Secondly, I thought I had tried that, but apparently I didn't do it right! – PA_Commons Jun 11 '14 at 22:13

Expected End of Statement in formula with quotes

1 Answers1

Linked