CODEWITHSUNDEEP
excelstringconcatenationrangevba6
HummBird
HummBird

Reputation: 77

Collect column range value combined in one cell followed by comma and end with & seperator

I am trying to get the collect combined value in one cell followed by comma and end with &
Column range : AM4:AM20 may be with value or blank.
I need to collect this value combined together with comma and with & seperator

AM4 = ABCD1

AM5 = ABCD2

AM6 = ABCD3

AM7 = ABCD4

AM8 = "" AM9 =""

eg., BB4 = ABCD1, ABCD2, ABCD3 & ABCD4

I tried this code

Sub GetPipeData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim collectedValues As String
    Dim addComma As Boolean

    ' Set the worksheet containing the values
    Set ws = ThisWorkbook.Sheets("INPUT") 

    ' Find the last used row in column AM
    lastRow = ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row

    ' Initialize the collectedValues variable
    collectedValues = ""
    addComma = False ' Initialize the flag to not add a comma

    ' Loop through the cells in column AM from row 4 to the last used row
    For Each cell In ws.Range("AM4:AM20" & lastRow)
        ' Check if the cell is not empty
        If Not IsEmpty(cell.Value) Then
            ' If addComma is True, add a comma before the value
            If addComma Then
                collectedValues = collectedValues & ", "
            End If
            ' Add the value to the collectedValues variable
            collectedValues = collectedValues & cell.Value
            ' Set addComma to True for the next iteration
            addComma = True
        End If
    Next cell

    ' Print the collected values in cell BB4
    ws.Range("BB4").Value = collectedValues
End Sub

Expected result

**BB4 = ABCD1, ABCD2, ABCD3 & ABCD
**
Please help me to achieve this task. Thank you :)

Upvotes: 1

Views: 53

Answers (2)

Jos Woolley
Jos Woolley

Reputation: 9062

Or you could just use a worksheet formula:

=LET(
    Φ,AM4:AM20,
    SUBSTITUTE(TEXTJOIN(", ",,Φ),", "," & ",MAX(1,SUM(N(Φ<>""))-1))
)

Upvotes: 2

Black cat
Black cat

Reputation: 6271

With the added Do...Loop you can get the required result.

Sub GetPipeData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim collectedValues As String
    Dim addComma As Boolean

    ' Set the worksheet containing the values
    Set ws = ThisWorkbook.Sheets("INPUT")

    ' Find the last used row in column AM
    lastRow = ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row

    ' Initialize the collectedValues variable
    collectedValues = ""
    'addComma = False ' Initialize the flag to not add a comma

    ' Loop through the cells in column AM from row 4 to the last used row
    For Each cell In ws.Range("AM4:AM20")
        ' Check if the cell is not empty
        If Not IsEmpty(cell.value) Then
            ' If addComma is True, add a comma before the value
            If addComma Then
                collectedValues = collectedValues & ", "
            End If
            ' Add the value to the collectedValues variable
            collectedValues = collectedValues & cell.value
            ' Set addComma to True for the next iteration
            addComma = True
        End If
    Next cell
    'This code added
    pos = 1
    Do While InStr(pos + 1, collectedValues, ",") <> 0
        pos = InStr(pos + 1, collectedValues, ",")
    Loop
    If pos<>1 Then collectedValues = Left(collectedValues, pos - 1) & " &" & Mid(collectedValues, pos + 1)
    'Till here
    ' Print the collected values in cell BB4
    ws.Range("BB4").value = collectedValues
End Sub

Upvotes: 1

Related Questions