How to embed variable into a cell formula

Here is my code:

For i = Z To lastRow
Range("$A" & i).Select
ActiveCell.FormulaRC1C = "='Sheet1'!$A" & i
etc, etc.

The last line is having a problem (obviously) because I can't seem to figure out the quotes needed. How do I format 开发者_如何学Pythonthe double quotes? Is there a better way to say this?

I don't think the quotes are the problem (they look OK). You're using the FormulaR1C1 method, which expects the address to be in R1C1 notation (e.g. R3C4), whereas you're supplying the address in e.g. C4.

Try using .Formula

There's no readable way to insert quote marks into code that generates a string. You can try triple-quotes - """ - and good luck with your debugging.

I tend to use the chr() function, with the knowledge that character 34 is the double-quote:

strQ = "He said: " & chr(34) & "It's a dead parrot" & chr(34) 
strQ = strQ  & ", and explained: "  & "It has ceased to be" & chr(34)  & "."


Debug.Print strQ

Will concatenate this string:

He said: "It's a dead parrot", and explained: "It has ceased to be".