How to split string into cells for multiple cells?

Question

I want my code to go through a list of cells containing names and split them up into the cells next to the original. I have some basic code to do the first bit, but I'm struggling to get it to cycle through the rest of my list, and also outputting it next to the original rather than in A1 as it does currently. I presume it's an issue with the 'Cell' part of the code but I can't quite fix it.

Sub NameSplit()

    Dim txt As String
    Dim i As Integer
    Dim FullName As Variant
    Dim x As String, cell As Range

    txt = ActiveCell.Value

    FullName = Split(txt, " ")

    For i = 0 To UBound(FullName)

        Cells(1, i + 1).Value = FullName(i)

    Next i


End Sub

Answer 1

Use a For Each loop on the range of name values. In this case, I just assumed they were in the first column but you can adjust accordingly:

Sub NameSplit()

Dim txt As String
Dim i As Integer
Dim FullName As Variant
Dim x As String, cell As Range

For Each cell In ActiveSheet.Range(Cells(1,1),Cells(ActiveSheet.UsedRange.Count,1))
     txt = cell.Value

     FullName = Split(txt, " ")

     For i = 0 To UBound(FullName)

         cell.offset(0,i + 1).Value = FullName(i)

     Next i

Next cell

End Sub

Answer 2

Make sure you are not trying to Split a blank cell and write all of the values in at once rather than nest a second For ... Next Statement.

Sub NameSplit()
    Dim var As Variant
    Dim rw As Long

    With Worksheets("Sheet1")   '<~~ you should know what worksheet you are on!!!!
        'from row 2 to the last row in column A
        For rw = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            'check to make the cell is not blank
            If CBool(Len(.Cells(rw, "A").Value2)) Then
                'split on a space (e.g. Chr(32))
                var = Split(.Cells(rw, "A").Value2, Chr(32))
                'resize the target and stuff the pieces in
                .Cells(rw, "B").Resize(1, UBound(var) + 1) = var
            End If
        Next rw
    End With
End Sub

If you are simply splitting on a space, have you considered a Range.TextToColumns method?

Sub NameSplit2()
    Dim var As Variant
    Dim rw As Long

    'disable overwrite warning
    Application.DisplayAlerts = False

    With Worksheets("Sheet1")   '<~~ you should know what worksheet you are on!!!!
        'from row 2 to the last row in column A
        With .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
            'Text-to-Columns with space delimiter
            .TextToColumns Destination:=.Cells(1, 2), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
                    Tab:=False, Semicolon:=False, Comma:=False, Other:=False, _
                    Space:=True
        End With
    End With

    Application.DisplayAlerts = True

End Sub