Looping Text to Columns Fixed Width on Individual Rows-CodePudding

I am attempting to build a loop that will look at each row in a column of data and split based on the first instance of an " ". I can get this to work on one line but the loop never activates. I tried my best at formatting this code but could not find a tutorial on how to have the commands appear as different colors and whatnot.

Dim num
Dim RowCnt As Integer
Dim x As Integer

ActiveCell.Select ' the cell to split
RowCnt = Range(Selection, Selection.End(xlDown)).Rows.Count 'determines #rows in column to split

With ActiveCell ' with block

   For x = 1 To RowCnt ' define # loops

         .Offset(, -1).FormulaR1C1 = "=FIND("" "",RC[1],1)" ' determine first " "
         num = .Offset(, -1).Value ' assign the number of chars to 'num' 

                Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(0, 1), Array(num, 1)), TrailingMinusNumbers:=True ' splits once based on 'num'
         
        .Offset(, -1).ClearContents ' clear 
        .Offset(1, 0).Activate

   Next x

End With
End Sub

CodePudding user response：

Use .offset(x,0).activate in last bit (just above next x)

all you're doing otherwise is activating the same original cell each time

You could achieve the same in 3 lines of code♦ (w/ for loop) using the following:

Sub test2()
    'Range("d2").Select
    With Selection
        .Offset(, 3).Formula2R1C1 = _
            "=LET(x_,RC[-3]:OFFSET(RC[-3],MATCH(0,IFERROR(SEARCH("""",RC[-3]:OFFSET(RC[-3],ROWS(C[-3])-ROWS(RC[-3])-1,0)),0),0)-1,0),IF(ISODD(SEQUENCE(1,2,1,1)),MID(x_,1,IFERROR(SEARCH("" "",x_)-1,LEN(x_))),IF(ISERROR(SEARCH("" "",x_)),"""",MID(x_,SEARCH("" "",x_) 2,LEN(x_)))))"
        Range(.AddressLocal, .End(xlDown).Offset(, 1)).Value = Range(Replace(.Offset(, 3).AddressLocal, "$", "") & "#").Value
        .Offset(, 3).ClearContents
    End With
End Sub

This uses the function:

=LET(x_,D2:OFFSET(D2,MATCH(0,IFERROR(SEARCH("",D2:OFFSET(D2,ROWS(D:D)-ROWS(D2)-1,0)),0),0)-1,0),IF(ISODD(SEQUENCE(1,2,1,1)),MID(x_,1,IFERROR(SEARCH(" ",x_)-1,LEN(x_))),IF(ISERROR(SEARCH(" ",x_)),"",MID(x_,SEARCH(" ",x_) 2,LEN(x_)))))

... which is an array function that reproduces the original list with relevant cells split as req.

♦ Office 365 compatibility; '3 lines' ignoring with/end/sub/etc.