Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Compile error

Posted on 2012-12-26
5
Medium Priority
?
141 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

580 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