Try this...


DestSh.Cells(3, "b").FormulaR1C1 = _
"=CONCATENATE(R[1]C,""_"",RC[-1]," & NameStr & ")"


"Simon W" wrote:

I've a macro that writes a CONCATENATE formula into a cell of an output
sheet. Could anyone tell me how i could incorporate the value of a
string variable created elsewhere in the macro as part of the formula?
At the moment i have something like the following, which gives me a
?#NAME error. It works with the first three elements of the concatenate
formula i.e. if I remove NameStr).
Thanks very much.



Dim NameStr As string

[NameStr is set elsewhere in the macro]
[DestSh is the output worksheet]

DestSh.Cells(3, "b").FormulaR1C1 = _
"=CONCATENATE(R[1]C,""_"",RC[-1],NameStr)"