VBA Concatenation script

[Jon_London]

Hello everyone,

I'm VERY new to VBA and am trying to achieve the following outcome:

I would like a range of cells (e.g. E1:E5) to be concatenated with CP-0001;, CP-0002; and CP-0003;. So, if the contents of cell E1 is 12345 I would like the output to be CP-0001;12345, CP-0002;12345 and CP-0003;12345 on three separate rows in my spreadsheet. The code should then look at cell E2 and repeat the process.

I've tried my very rudimentary code below, but it obviously doesn't work! Could anyone please help me?

Sub vba_concatenate()

Range("A1,A100") = "CP-0001;" & Range("E1,E100")
Range("A1,A100") = "CP-0002;" & Range("E1,E100")
Range("A1,A100") = "CP-0003;" & Range("E1,E100")

End Sub

Many thanks
Jon

 

[Phuoc]

Try this

Sub vba_concatenate()

With Range("A1:A100")
.FormulaR1C1 = "=""CP-0001;""&RC[4]&"", ""&""CP-0002;""&RC[4]&"", ""&""CP-0003;""&RC[4]"
.Value = .Value
End With

End Sub

 

[Jon_London]

Thanks so much, Phuoc.
The script almost works, but the output is e.g.
CP-0001;12345, CP-0002;12345, CP-0003;12345 when I needed it to be on three separate rows e.g.

CP-0001;12345
CP-0002;12345
CP-0003;12345

Apologies, I probably didn't make this clear in my initial post. What do I need to amend to make this possible?

Many thanks
Jon

 

[Jimmypop]

Try this... Put CP-0001; in cell T1, CP-0002; in T2 and CP-0003; in T3 and your items in E1:E5

Sub vba_concatenate()

    ActiveCell.FormulaR1C1 = _
        "=CONCAT(R1C20,RC[4],"" "",R2C20,RC[4],"" "",R3C20,RC[4])"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A100"), Type:=xlFillDefault
    Range("A1:A100").Select
 
End Sub

 

[Jimmypop]

Try this... Put CP-0001; in cell T1, CP-0002; in T2 and CP-0003; in T3 and your items in E1:E5

Sub vba_concatenate()

    ActiveCell.FormulaR1C1 = _
        "=CONCAT(R1C20,RC[4],"" "",R2C20,RC[4],"" "",R3C20,RC[4])"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A100"), Type:=xlFillDefault
    Range("A1:A100").Select
 
End Sub

Cancel above... need to redo

 

[Jimmypop]

Apologies... not getting right... will maybe need to use a For Loop to do each row.. I am still learning on that side...

 

[Jon_London]

No problem, thank you for trying

 

[Phuoc]

Update code:

Sub vba_concatenate_V2()
With Range("A1:A100")
.FormulaR1C1 = "=""CP-0001;""&RC[4]&CHAR(10)&""CP-0002;""&RC[4]&CHAR(10)&""CP-0003;""&RC[4]"
.Value = .Value
.WrapText = True
End With

End Sub

 

[Jon_London]

Great, thank you so much, Phuoc!