Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using the value of a macro variable in a concatenate formula
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)" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using the value of a macro variable in a concatenate formula
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)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using the value of a macro variable in a concatenate formula
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)" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using the value of a macro variable in a concatenate formula
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using the value of a macro variable in a concatenate formula
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate Variable Array of Text Cells | Excel Discussion (Misc queries) | |||
Macro - Formula RC[?] with ? being a variable number | Excel Discussion (Misc queries) | |||
Concatenate column with variable to set a range | Excel Discussion (Misc queries) | |||
Macro Sum Formula For a Variable Rang | Excel Programming | |||
Help with macro formula and variable | Excel Worksheet Functions |