?
Solved

Excel Compile error

Posted on 2012-12-26
5
Medium Priority
?
138 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 143

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

765 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