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

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

HIH
--
AP

"Simon W" a écrit dans le message de
oups.com...
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)"

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.

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

HTH
--
AP

"Simon W" a écrit dans le message de
ups.com...
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.

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