using the value of a macro variable in a concatenate formula

[Simon W]

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

 

[Ardus Petus]

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

HIH

 

[DaveO]

Try this...


DestSh.Cells(3, "b").FormulaR1C1 = _

"=CONCATENATE(R[1]C,""_"",RC[-1]," & NameStr & ")"

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

 

[Simon W]

thanks for the reply - unfortunately it gives me an 'application
defined of object defined error'. The concatenate formula works without
" & NameStr & " and NameStr is defined as i can put it into a different
cell not as part of the concatenate formula. Very grateful for any
suggestions.

 

[Ardus Petus]

I guess NameStr has been defined as a name on the worksheet.
If this is true, use Range(Namestr).value instead

HTH