Concatenate for VBA

[beginvbaanalyst]

Hi Everyone,

I'm creating a concatenate for an unlimited range (1048558 rows) and thought I had this correct, but turns out it's not working for me.

Sub ConcatenateArticleAssortment()
Dim AssortmentRows As Long
Dim ArticleRows As Long
AssortmentRows = Range("B" & Rows.Count).End(xlUp).Row
ArticleRows = Range("A" & Rows.Count).End(xlUp).Row
Range(ArticleRows & AssortmentRows).FormulaR1C1 = "=CONCATENATE(RC[-2],""-"",RC[-1])"
End Sub

The long that fails is Range(ArticleRows & AssortmentRows).FormulaR1C1 = "=CONCATENATE(RC[-2],""-"",RC[-1])"
I get an error of

What does this mean? Am I unable to do a concatenate like this?
Thank you for all the help this community has been giving me, you guys are amazing!

 

[Fluff]

What are you trying to do?

 

[beginvbaanalyst]

What are you trying to do?

Take ranges column A and B to create a concatenate until the last filled cell in each column.
I don't understand the error.

The concatenate will help with a manual validation step.

 

[Fluff]

Ok, how about

Sub beginvba()
   Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=rc[-2]&""-""&rc[-1]"
End Sub

 

[beginvbaanalyst]

Ok, how about

Sub beginvba()
   Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=rc[-2]&""-""&rc[-1]"
End Sub

I realized I didn't give the formula a destination.
How does it know to take Column B when there's not a letter B in the Code?
Throwing me off a little.
Thank you for helping

 

[Fluff]

The rc[-1] is basically saying this row & 1 column to the left

Excel Formulas - A1 or R1C1 Notation

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

 

[beginvbaanalyst]

The rc[-1] is basically saying this row & 1 column to the left

Excel Formulas - A1 or R1C1 Notation

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

Thank you for the explanation, I know exactly what I did that messed this up.
Have a great weekend Fluff!

 

[Fluff]

You're welcome & thanks for the feedback