Solved

Excel Compile error

Posted on 2012-12-26
5
135 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
Problem to line 23 53
Need VBA code to change date format of Column A 5 23
How to change the format as number 18 30
Need help with Clear Macro 4 23
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

792 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