Expected End of Statement in formula with quotes

excel vba excel-2011
PA_Commons picture PA_Commons · Jun 11, 2014 · Viewed 33.8k times · Source 
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

Tim Williams picture Tim Williams · Jun 11, 2014

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

Related questions

Disable all dialog boxes in Excel while running VB script?

I have some code in VB that saves all XLSM files as XLSX. I already have the code that will do that for me, but dialog boxes show up for every action. This was fine for a few dozen files. …

Read More
How can I convert a range to a string (VBA)?

What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of …

Read More
Solver.xlam is missing?

I created a program in Excel 2010 on my PC that relies on the built-in solver for Excel. I made sure it worked on both Excel 2010 and Excel 2013 for PC. I then tried to use this same program on Excel 2011, and …

Read More