Output selection to a single string

[cc9083]

I have multiple rows containing list of serials as below.

A2004 / A2018 / A2038 / A2122 / A2166

I would like to format these serials in such that they are concatenated into a single string with each serials separated by a comma and the results shall be positioned at the last column of each row. Also the string must have an (M&E) text at the end. So the result should look like this.

A2004, A2018, A2038, A2122, A2166 (M&E)

Below is the code for input from a single column instead of rows. How do I tweak to get my desired result.

Sub test()
For Each UsrCell In Selection
a = a & UsrCell.Value & ";"
Next UsrCell
a = Left(a, Len(a) - 1)
Open "d:\work\testfile.txt" For Output As #1 'Change your path here
Print #1, a;
Close #1
End Sub

[Chance2]

It looks like you can just add to your final "a" string...

a = Left(a, Len(a) - 1) & " (M&E)"

[cc9083]

Hi Chance 2. Thanks for your reply. I'm relatively new to VBA and will need further explanation. I would like to have a button to perform the task and input the results in Column K of my attachment. Kindly help.

[Chance2]

This code will add the data to column K

Sub test()
Dim iRow As Long
Dim iCol As Long

iRow = 2

Do Until IsEmpty(Range("A" & iRow))
    Range("K" & iRow) = ""
    For iCol = 1 To 10
        If Len(Cells(iRow, iCol)) > 0 Then
            Range("K" & iRow) = Range("K" & iRow) & "," & Cells(iRow, iCol)
        End If
    Next
    Range("K" & iRow) = Mid(Range("K" & iRow), 2) & " (M&E)"
    iRow = iRow + 1
Loop

End Sub

[cc9083]

Thanks Chance2. Made some tweaks and works like a charm.