asked on Excel Compile error
When I use =
TRIM(CONCATENATE(AK2," ",AQ2)) and drag down, it works fine
but I need to use this in a macro and when I use
Sub SetCode()
Range("AP2").Select
ActiveCell.FormulaR1C1 = _
"=TRIM(CONCATENATE(AK2," ",AQ2))"
FillDown
End Sub
I'll get a compile error * Expected: end of statement
If I change the code to
"=TRIM(CONCATENATE(AK2,"" "",AQ2))"
I get past the error and the macro will run but it will change the code to
"=TRIM(CONCATENATE('AK2',"
and it just fills the column with the #NAME
Can anyone tell me how to fix this ?
TRIM(CONCATENATE(AK2," ",AQ2)) and drag down, it works fine
but I need to use this in a macro and when I use
Sub SetCode()
Range("AP2").Select
ActiveCell.FormulaR1C1 = _
"=TRIM(CONCATENATE(AK2," ",AQ2))"
FillDown
End Sub
I'll get a compile error * Expected: end of statement
If I change the code to
"=TRIM(CONCATENATE(AK2,"" "",AQ2))"
I get past the error and the macro will run but it will change the code to
"=TRIM(CONCATENATE('AK2',"
and it just fills the column with the #NAME
Can anyone tell me how to fix this ?
Microsoft Excel
Last Comment
GD_GRAY
ASKER
How would I work around this
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
FYI you could also simply have changed the code to use .Formula instead of .FormulaR1C1
If you use the latter, you have to pass a formula in R1C1 style referencing and not A1 style. (2003 and earlier were more forgiving about that)
If you use the latter, you have to pass a formula in R1C1 style referencing and not A1 style. (2003 and earlier were more forgiving about that)
ASKER
I was able to work it out via record macro.
>"=TRIM(CONCATENATE(AK2,""
>I get past the error and the macro will run
and it should be like this indeed.
if you look after the code, the value in the cell will be indeed:
=TRIM(CONCATENATE(AK2," ",AQ2))
the "" in the vba code is only because " is already a string delimiter, and the duplicated double quotes is to "escape" the double quotes.