How to Split more than the Active Cell

Question

I am trying to get a cell to split down the based on the values returned from a formula. It wont let me upload a photo so I will do the best I can to explain.

The following is my code to split the cell:

Sub splitText()
Dim splitVals As Variant, totalVals As Long, I As Integer
splitVals = Split(ActiveCell.Value, " ")
totalVals = UBound(splitVals)
range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row,ActiveCell.Column + 1 + totalVals)).Value = splitVals
End Sub

I am wondering if there is a way to get my program set so that it will split all of the cells in column D, instead of just the active cell.

[this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) might help you — litelite, Jul 22 '16 at 19:50
Look into "For Loops vba" or "Do Loops vba" or "Loop through rows vba". Google will have a ton of examples. — Scott Craner, Jul 22 '16 at 19:56

score 0 · Answer 1 · answered Jul 22 '16 at 20:09

0

Here, try this:

    Sub splitText()

    Dim r As Range
    Dim splitVals As Variant, totalVals As Long, I As Integer

    For Each r In Range("D:D").Cells
        If r.Value <> "" Then
            splitVals = Split(r.Value, " ")
            totalVals = UBound(splitVals)
            Range(Cells(r.Row, r.Column + 1), Cells(r.Row, r.Column + 1 + totalVals)).Value = splitVals
        End If
    Next r


    End Sub

answered Jul 22 '16 at 20:09

Kerry White

416
2
10

thank you so much! This totally makes sense, but now it is not returning anything... I don't know if it makes a difference or not, but the data in my D cells is four digit numbers separated by spaces. – kmja2500 Jul 22 '16 at 20:15

Eric K. · Answer 2 · 2016-07-26T14:24:28.747

0

Sub splitText()
   Dim splitVals As Variant, totalVals As Long
   Dim N as single
   splitVals = Split(ActiveCell.Value, " ")
   totalVals = UBound(splitVals)
   For N = 0 to totalVals
      ActiveCell.offset(0, 1 + N) = splitVals(N)
      ActiveCell.offset(1, 0).select
   Next N
End Sub

This sub is only to split text for a single cell, which is your active cell. If you need to split for multiple cells, then will need to loop this function for each cells.

edited Jul 26 '16 at 14:24

answered Jul 23 '16 at 05:51

Eric K.

814
2
13
22

ok thanks! That makes sense... but it is still only splitting the active cell. I have tried modifying this code to get it to split the cells down column D beginning at row 5, but can not seem to get it. – kmja2500 Jul 26 '16 at 14:19
yes, only for activecell, because we are using ActiveCell, add in new code to move the active cell to next row – Eric K. Jul 26 '16 at 14:23
but my recommendation is using Cells(), rather using ActiveCell, keep going – Eric K. Jul 26 '16 at 14:26

How to Split more than the Active Cell

2 Answers2