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 ?

Guy Hengel [angelIII / a3]

>If I change the code to
>"=TRIM(CONCATENATE(AK2,"" "",AQ2))"
>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.

GD_GRAY

ASKER

How would I work around this

ASKER CERTIFIED SOLUTION

GD_GRAY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

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)

GD_GRAY

ASKER

I was able to work it out via record macro.