problem with VBA apostrophes

Question

Single line of code is causing my application to stop working - I think that the problem is with the apostrophes (maybe wrong escape):

.Formula = "=IF(AND(" 'Criterion " & i & "'!" & cellAdress & ">=1;"'Criterion " & i & "'!" & cellAdress & "<=4);"'Criterion " & i & "'!" & cellAdress & ";0)"

When I try to enter the "' sequence VBA automatically puts a space between " and ' making the latter appear as comment. What is wrong - do I need to use escape character here? If yes how to code it. I get an error 1004 Application of object-defined error.

Thank you

Answer 1

You escape double quotes in VBA by putting 2 double quotes, like so:

""

You can also explicitly call Chr(34) to put in double quotes, like so:

Debug.Print "The following is in double quotes: " & _
    chr(34) & "Inside double quotes" & chr(34)

I wasn't able to try out the following formula in Excel, but I think it should work:

.Formula = "=IF(AND(""Criterion""" & i & "!" & cellAdress & _
  ">=1;""Criterion""" & i & "!" & cellAdress & "<=4);""Criterion""" & _
  i & "!" & cellAdress & ";0)"