CODEWITHSUNDEEP
vbaexcel
Dylan
Dylan

Reputation: 25

Using a split function in a Loop not working

I am receiving no output from the following code:

Sub spliter()

Dim text As String
Dim a As Integer
Dim name As Variant

Do Until IsEmpty(ActiveCell)
     text = ActiveCell.Value
     name = Split(text, " ")
             For a = 0 To UBound(name)
             Cells(1, a + 1).Value = name(a)


             Next a
    ActiveCell.Offset(1, 0).Select
Loop
End Sub

Using the 'run to' debugger, I can see the loops working fine. This was build originally as a splitter sub, with the Loop function shelled into it. The splitter sub works fine with 1 cell and by itself, but as I incorporated the Loop, the splitter function delivers nothing. I think this may be a array in an array issue.

Upvotes: 0

Views: 315

Answers (2)

R3uK
R3uK

Reputation: 14537

This should solve your problems :

Sub spliter()

Dim vText As String, _
    vName() As String, _
    wRow  As Integer

wRow = 1
If IsEmpty(ActiveCell) Or ActiveCell.Value2 = vbNullString Then
    MsgBox "ActiveCell is empty!", vbCritical + vbOKOnly
Else
    Do Until IsEmpty(ActiveCell) Or ActiveCell.Value2 = vbNullString
        vText = ActiveCell.Value
        vName = Split(vText, " ")
           For a = LBound(vName) To UBound(vName)
              Cells(wRow, a + 1).Value = vName(a)
           Next a
        ActiveCell.Offset(1, 0).Activate
        wRow = wRow + 1
    Loop
End If

End Sub

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

I'm not sure where you're trying to put the data - you get the data from the ActiveCell which may be anywhere on the sheet and you paste it on row 1 of the same sheet.

This code will take the ActiveCell and cells below that and split the text string by the space and place each word next to the original sentence.

Sub Splitter()

Dim sText As String
Dim x As Integer
Dim vName As Variant

Do Until IsEmpty(ActiveCell)
    sText = ActiveCell.Value
    vName = Split(sText, " ")
    ActiveCell.Offset(, 1).Resize(, UBound(vName) + 1) = vName
    ActiveCell.Offset(1, 0).Select
Loop

End Sub

Upvotes: 1

Related Questions