Solved

Excel Compile error

Posted on 2012-12-26
5
132 Views
Last Modified: 2012-12-31
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 ?
0
Comment
Question by:GD_GRAY
  • 3
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38721917
>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.
0
 

Author Comment

by:GD_GRAY
ID: 38721952
How would I work around this
0
 

Accepted Solution

by:
GD_GRAY earned 0 total points
ID: 38722050
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]))
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38723084
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)
0
 

Author Closing Comment

by:GD_GRAY
ID: 38732205
I was able to work it out via record macro.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now