Concatenate macro Type Mismatch error

Newbie to macros and VBA here. I am attempting to concatenate several columns and I am getting the Type Mismatch error. I understand why I am getting this error, but I do not know any other way to accomplish what I am seeking. Any help would be greatly appreciated.

Sub Concatenate
    LR = Range("A1").End(xlDown).Offset(1, 0).Row
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "Combined Account"
    Selection.Font.Bold = True
    Range("AA2:AA" & Range("A" & Rows.Count).End(3)(1).Row).FormulaR1C1 = _
    "=CONCATENATE(A1,"-",R1,"-",S1,"-",T1,"-",G1)"       'type mismatch error pops here
    Range("AA2:AA" & LR).Value = Range("AA2:AA" & LR).Value
    Range("A1").Select
End Sub

Thanks in advance,
Ray

I think you forgot your double quotes.

Sub rdowney79zaaz()
    Lr = Range("A1").End(xlDown).offset(1, 0).Row
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "Combined Account"
    Selection.Font.Bold = True
    Range("AA2:AA" & Range("A" & Rows.count).End(3)(1).Row).Formula = _
    "=CONCATENATE(A2,"" - "",R2,"" - "",S2,"" - "",T2,"" - "",G2)" 'type mismatch error pops here
    Range("AA2:AA" & Lr).Value = Range("AA2:AA" & Lr).Value
    Range("A1").Select
End Sub

Hi - you are trying to use A1 style references for the R1C1 formula, you either need to change formular1c1 to formula or change the references from A1 to R1C1.

You also need to double up quote marks. I would suggest:

Sub Concatenate()
Dim LR
    LR = Range("A1").End(xlDown).Offset(1, 0).Row
    Range("AA1").FormulaR1C1 = "Combined Account"
    Selection.Font.Bold = True
    With Range("AA2:AA" & Range("A" & Rows.Count).End(3)(1).Row)
        .Formula = "=CONCATENATE(A1,"" - "",R1,"" - "",S1,"" - "",T1,"" - "",G1)"     'type mismatch error pops here
        .Value = Range("AA2:AA" & LR).Value
    End With
End Sub

Originally Posted by Kaper

Hi John and ragulduy,

Is there any reason why you both added spaces at both sides of dash:

"" - ""

while in original there was only:

"-"

of course double quotes are needed (I added them too) but why spaces around?

No. The extra spaces were added when I pasted the code from the thread.

Thank you fellas, they all worked. But it brought up another issue. I would like this macro to:
1. Insert the concatenate formula only if cell AA is blank. This is a cumlative monthly file and I dont want to redo previous months, only perform on newly added data.
2. If column R is blank, I would like the macro to skip that row entirely.

Is this possible?

Thanks in advance,
Ray

If you copy the code in post 1 into the vba editor it will add the spaces, I guess it segragates the parts in quotation marks (i.e. "=concatenate(A1," from the dashes and spaces them apart from the "-" which it is reading as subtraction signs.

You get the same thing if you type:
a=1+2
the VBA editor will adjust it to
a = 1 + 2

I hadn't noticed the change when copying back to the post.

Issue Solved. Even though its way above my level of understanding, it worked great Kaper!

Thank you very much Kaper, JHD and Ragulduy for your help.

Thanks,
Ray