CODEWITHSUNDEEP
vbaexceltweak
Namorg
Namorg

Reputation: 37

Microsoft Excel VBA text split tweak

I have an Excel sheet that uses a macro to split a string of text when it is scanned (via plug&play barcode scanner) into the first column to the right using the space character as a delimiter. Example picture of Excel sheet followed by Excel macro.

excel

Sub textsplit(rng As Range)
    Dim c As Range, Arr

    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            Arr = Split(c.Value, " ")
            c.Offset(0, 1).Resize(1, UBound(Arr) + 1).Value = Arr
        End If
    Next c
End Sub

Now all of this works perfectly, however I need to make a little tweak to this. I would like the macro to skip the column after the first serial ("CNA1234567") and leave it blank. How can I tweak this code to do this?

Upvotes: 1

Views: 201

Answers (3)

LimaNightHawk
LimaNightHawk

Reputation: 7093

This method is a little more explicit and will give you all the control you want.

Sub textsplit(rng As Range)
    Dim c As Range
    Dim r As Long
    Dim Arr As Variant

    For Each c In rng.Cells
        If Len(c.Value) > 0 Then

            r = c.Row

            Arr = Split(c.Value, " ")
            With Sheet1
                .Cells(r, 2).Value = Arr(0)
               '.Cells(r, 3).Value = <--- skipped 
                .Cells(r, 4).Value = Arr(1)
                .Cells(r, 5).Value = Arr(2)
                .Cells(r, 6).Value = Arr(3)
                .Cells(r, 7).Value = Arr(4)
                .Cells(r, 8).Value = Arr(5)
           End With
        End If
    Next c
End Sub

Upvotes: 3

John Coleman
John Coleman

Reputation: 52008

This works:

Sub textsplit(rng As Range)
    Dim c As Range, Arr As Variant
    Dim i As Long

    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            Arr = Split(c.Value, " ")
            For i = 0 To UBound(Arr)
                c.Offset(0, IIf(i > 0, i + 2, i + 1)).Value = Arr(i)
            Next i
        End If
    Next c
End Sub

If rng is large you can turn screen updating first off then back on.

Upvotes: 1

MatthewD
MatthewD

Reputation: 6801

3Whatever number that column is just check that you are not on it.

Sub textsplit(rng As Range)
    Dim c As Range, Arr

    For Each c In rng.Cells
        If c.Column <> 3 Then
            If Len(c.Value) > 0 Then
                Arr = Split(c.Value, " ")
                c.Offset(0, 1).Resize(1, UBound(Arr) + 1).Value = Arr
            End If
        End if
    Next c
End Sub

Upvotes: 0

Related Questions