CODEWITHSUNDEEP
excelvba
Mr_Thomas
Mr_Thomas

Reputation: 869

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 the double quotes? Is there a better way to say this?

Upvotes: 2

Views: 1255

Answers (2)

Nigel Heffernan
Nigel Heffernan

Reputation: 4726

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".

Upvotes: 0

Gary McGill
Gary McGill

Reputation: 27516

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

Upvotes: 5

Related Questions