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.

Answer 1

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