Need help on CONCATENATE formula

[Guitarde]

Hello,

I am trying to add via VBA a concatenate formula
I want the first element to be a variable and the 2nd to refer to a range
I do not want to return just text but really link to my cell named 'rInvoice'

if I concatenate 2 named range it works
ActiveCell.FormulaR1C1 = "=CONCATENATE(rText,rInvoice)"


But I get a Run-time error if I try to insert variables like this:

Var = "Invoice number : "
ActiveCell.FormulaR1C1 = "=CONCATENATE(" & Var & ",rInvoice)"


I am probably missing some double "" somewhere but just can't seem to figure it out...

Thanks
Eric

 

[Joe4]

Try:

ActiveCell.FormulaR1C1 = "=CONCATENATE(" & Chr(34) & Var & Chr(34) & ",rInvoice)"

or

ActiveCell.FormulaR1C1 = "=CONCATENATE(""" & Var & """,rInvoice)"

 

[Guitarde]

Thanks Joe, this works great.
I thought I was missing a double quote somewhere, tried many things but never triple ones.

 

[Joe4]

You are welcome!
Glad I was able to help!

Yeah, that triple quotes thing is a bit confusing to me, which is why I often use Chr(34) instead, which in the ASCII code for literal double-quotes.