I have an excel programming problem I can't seem to get. I have a spreadsheet with the following cell formula that works fine:
=IF(C20="","No Quote",RANK(C20,$B20:$N20,1))

In an VBA Macro I have a similar formula without the If Statement, that again works fine:

ActiveCell.FormulaR1C1 = "=RANK(R[-10]C,R[-10]C2:R[-10]C20,1)"

My problem is I need to combine the two. For some reason my syntax seems off and my VBA editor will not accept it. Here is my best guess:

ActiveCell.FormulaR1C1 = "=IF(R[-10]C="", "No Quote",RANK(R[-10]C,R[-10]C2:R[-10]C20,1))"

What am I missing that the debugger will not accept?

Have you tried to use

"ActiveCell.Formula = "

instead of

"ActiveCell.FormulaR1C1 = "

you can enter the formula in the macro just like you would in the cell.

Tried that and didn't work. It seesms to be expecting an end of statement where the comma appears before 'No Quote'

I guess you have a problem with the quotes. When you place your formula within the quotes, the quote in your formula need a special treatment.

Replace the quotes in your formula with
" & CHR(34) & "

ActiveCell.FormulaR1C1 = "=IF(R[-10]C=" & CHR(34) & CHR(34) & ", " & CHR(34) & "No Quote" & CHR(34) & ",RANK(R[-10]C,R[-10]C2:R[-10]C20,1))"

Alternatively: Replace a single double-quote with a double double-quote. It looks strange, but VBA interprets a double-double quote in a string as CHR(34)

ActiveCell.FormulaR1C1 = "=IF(R[-10]C="""", ""No Quote"",RANK(R[-10]C,R[-10]C2:R[-10]C20,1))"

Regards,
Jamon