Use of Double quotes in VBA IF Statement

[steveski]

I want to test a field to see if it is empty. In a spreaadsheet, I can
use double quotes, but in VBA it doesn't work. Can you help?

Target(1, 5).FormulaR1C1 = "=IF(RC9="","","Not Empty")"

 

[Peo Sjoblom]

You have to use extra quotes

"=IF(RC9="""","""",""Not Empty"")"

 

[Dave Peterson]

And from a tip by Tom Ogilvy:

Dim myStr As String
myStr = "=IF(RC9=@@,@@,@Not Empty@)"
myStr = Application.Substitute(myStr, "@", Chr(34))
ActiveCell.FormulaR1C1 = myStr