VBA Not Interpreting Single Quotes Correctly

[Mayanwolfe]

Hello,

As part of a larger macro, I'm trying to input a formula into a range of cells. This formula does an indirect lookup of a worksheet name as a tab name, and therefore requires single quotes. However, VBA keeps trying to interpret the first single quote as a comment, and therefore commenting out the rest of the formula. I've tried using Chr(39) instead, but it still gives me run time error 1004. How can I reformat this formula to make it work within the macro, and still paste correctly into the cell range?

=IFERROR(IFNA(VLOOKUP(E11,INDIRECT("'"&$D11&"'!"&"$E$10:$S$120"),15,FALSE),VLOOKUP(E11,INDIRECT("'"&$D11&"'!"&"$D$10:$S$120"),16,FALSE)),0)

Thank you for any help.

 

[RoryA]

You have to double up any " marks that are part of the actual formula string:

.Formula = "=IFERROR(IFNA(VLOOKUP(E11,INDIRECT(""'""&$D11&""'!""&""$E$10:$S$120""),15,FALSE),VLOOKUP(E11,INDIRECT(""'""&$D11&""'!""&""$D$10:$S$120""),16,FALSE)),0)"

 

[Fluff]

Can you please post the code that you are using to insert the formula?

Beaten2it

 

[Mayanwolfe]

Thanks Rory! That did it. I had already tried the double quotes thing also, but I put them in the wrong place. Now I understand how to do it properly! I really appreciate your help.