CODEWITHSUNDEEP
excelvbaloopssplit
Brandon M.
Brandon M.

Reputation: 29

How to Split text and loop to next row

I'm using the split function to split text using spaces. I have gotten my macro to split the text but I am having difficulties getting the loop to move to the next row below to split.

Sub Split_Text_Test1()

Dim Txt As String
Dim i As Integer
Dim FullName As Variant

Range("A1").Select

Txt = ActiveCell.Value

FullName = Split(Txt, " ")

For i = 0 To UBound(FullName)
    Cells(1, i + 1).Value = FullName(i)
Next i

End Sub

Upvotes: 0

Views: 799

Answers (4)

tomi09pl
tomi09pl

Reputation: 58

I added few thing to your code, see if that serves your purpose. However, as SJR said Text to columns option in Data menu would do the same with less effort. 

Sub Split_Text_Test1()

Dim Txt As String
Dim i As Integer
Dim FullName As Variant
Dim lastRow As Long
Dim myRange As Range

With ActiveSheet.UsedRange
        lastRow = .Rows(.Rows.Count).Row
End With
Debug.Print lastRow

'Range("A1").Select

Set myRange = ActiveSheet.Range("A1", "A" & lastRow)

    For Each cell In myRange

        Txt = cell.Value

        FullName = Split(Txt, " ")

            For i = 0 To UBound(FullName)
                Cells(cell.Row, i + 1).Value = FullName(i)
            Next i
    Next cell

End Sub

Upvotes: 0

Wilhelm
Wilhelm

Reputation: 306

In this case I would use a loop (and your solution was not that far from this):

Dim Txt As String
Dim i As Integer
Dim FullName As Variant
Dim R As Integer, C As Integer, MaxR as Integer
C = 1 ' can be another loop as well
For R = 1 to 1000

Txt = Trim(Cells(r,1).Value) ' will remove space from start and end
FullName = Split(Txt, " ")

For i = 0 To UBound(FullName)
    Cells(R , C + 1 + i ).Value = FullName(i)
Next i

Next R

Upvotes: 0

SJR
SJR

Reputation: 23081

You probably need to change the bit inside your loop thus as you are starting at A1. This assumes you want the entries in A2 and down. Not generally advisable to use Select/Activate, not very robust.

Edited to move across columns rather than down rows.

For i = 0 To UBound(FullName)
    Range("A1").Offset(,i + 1).Value = FullName(i)
Next i

That said, you can avoid a loop altogether and use

Range("B1").Resize(, UBound(FullName) + 1).Value = FullName

Upvotes: 1

Wilhelm
Wilhelm

Reputation: 306

Sub Split_Text_Test1()

Dim Txt As String
Dim i As Integer
Dim FullName As Variant
Dim R As Integer, C As Integer
Range("A1").Select ' assumes that the cells below that are empty 

Txt = ActiveCell.Value

FullName = Split(Txt, " ")
R = ActiveCell.Row
C = ActiveCell.Column
For i = 0 To UBound(FullName)
    Cells(R + 1 + i, C).Value = FullName(i)
Next i

End Sub

Upvotes: 0

Related Questions