CODEWITHSUNDEEP
excelvba
Mahata Tata
Mahata Tata

Reputation: 1

Combining cell values with VBA

My dataset is many thousand rows, with combinations varying from 1 cell to 15 cells.
Each combination is separated by a blank cell.
I would like VBA to automate this process throughout the whole column.

Column 2 is what I would like to do.
enter image description here

Sub Concat()
Dim i As Integer, Sht As Worksheet, Str As String
i = 3
Set Sht = ThisWorkbook.Sheets(2)
Str = Sht.Cells(2, 1).Value
Do Until Sht.Cells(i, 1).Value = "999999"
    Do Until Sht.Cells(i, 1).Value = ""
        Str = Str & "-" & Sht.Cells(i, 1).Value
        i = i + 1
    Loop
    Sht.Cells(i, 1).Value = Str

    'Here i need the string to reset and start over
    ' at the cell under the cell where stringvalue was pasted

Loop
End Sub

Upvotes: 0

Views: 179

Answers (2)

Mahata Tata
Mahata Tata

Reputation: 1

Sub Concat_numbers()

Dim i As Integer, Sht As Worksheet, Str As String
i = 3
Set Sht = ThisWorkbook.Sheets(1)
Str = Sht.Cells(2, 1).Value

Do Until Sht.Cells(i, 1).Value = "999999"


Do Until Sht.Cells(i, 1).Value = ""

Str = Str & "-" & Sht.Cells(i, 1).Value
i = i + 1
Loop


Sht.Cells(i, 2).Value = Str

i = i + 1
Str = Sht.Cells(i, 1).Value
i = i + 1

Loop

End Sub

Upvotes: 0

rustyBucketBay
rustyBucketBay

Reputation: 4561

Find this little code that would concat your first 4 values.

Sub Concat()
    Dim i As Integer
    Dim Sht As Worksheet
    Dim Str As String
    Set Sht = ThisWorkbook.Sheets(1)
    Str = ""
    For i = 1 To 4
        Str = Str & Sht.Cells(i, 1).Value
        If Not i = 4 Then
            Str = Str & "-"
        End If
    Next i
    Sht.Cells(i, 2).Value = Str
End Sub

enter image description here

From here you can adapt it to do specifically what you need.

Edit: Find the code

Sub Concat()
    Dim i As Integer
    Dim Sht As Worksheet
    Dim Str As String
    Set Sht = ThisWorkbook.Sheets(1)
    Str = ""
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row + 1
        If Sht.Cells(i, 1).Value = "" Then
            Str = Left(Str, Len(Str) - 1)
            Sht.Cells(i, 2).Value = Str
            Str = ""
        Else
            Str = Str & Sht.Cells(i, 1).Value & "-"
        End If
    Next i
End Sub

Upvotes: 0

Related Questions