• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

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()
    ActiveCell.FormulaR1C1 = _
        "=TRIM(CONCATENATE(AK2," ",AQ2))"
End Sub

I'll get a compile error * Expected: end of statement
If I change the code to
I get past the error and the macro will run but it will change the code to
and it just fills the column with the #NAME

Can anyone tell me how to fix this ?
  • 3
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>If I change the code to
>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:

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_GRAYAuthor Commented:
How would I work around this
GD_GRAYAuthor Commented:
Made  work around via Record Macro. I set to record macro than did the drag and than used that formula :

=TRIM(CONCATENATE(RC[-5],"" "",RC[1]))
Rory ArchibaldCommented:
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_GRAYAuthor Commented:
I was able to work it out via record macro.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now