GD_GRAY
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'," ",'AQ2'))"
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 ?
ASKER
How would I work around this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.