Text to column for loop

Question

So basically I have multiple columns of text that I want to change to numbers. Now the issue i face is ive got the code but i dont understand how to run a loop on it to choose the next third column. This is my code:

Sub Texscolumn()

Range("AI2:AI96").Select
Selection.TextToColumns Destination:=Range("AI2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 9), Array(7, 9), Array(8, 9)), TrailingMinusNumbers:= _
    True
 End Sub

So the next column I want is suppose AJ2:AJ96. I cant seem to figure out how to make a for loop with changing columns like this.

R3uK · Accepted Answer · 2017-02-01T11:25:13.050

At least 2 options :

Sub Texscolumn()
Dim j As Integer
With Sheets("sheet1")
    For j = 35 To 36
        .Range(.Cells(2, j), .Cells(96, j)).TextToColumns Destination:=.Cells(2, j), _
                            DataType:=xlDelimited, _
                            TextQualifier:=xlDoubleQuote, _
                            ConsecutiveDelimiter:=True, _
                            Tab:=True, _
                            Semicolon:=False, _
                            Comma:=False, _
                            Space:=True, _
                            Other:=False, _
                            FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 9), Array(7, 9), Array(8, 9)), _
                            TrailingMinusNumbers:=True
    Next i
End With
End Sub

or

Sub Texscolumn()
Dim Rg As Range
Dim i As Integer
Set Rg = Sheets("sheet1").Range("AI2:AI96")
With Rg
    For i = 1 To 2
        .TextToColumns Destination:=.Cells(1, 1), _
                            DataType:=xlDelimited, _
                            TextQualifier:=xlDoubleQuote, _
                            ConsecutiveDelimiter:=True, _
                            Tab:=True, _
                            Semicolon:=False, _
                            Comma:=False, _
                            Space:=True, _
                            Other:=False, _
                            FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 9), Array(7, 9), Array(8, 9)), _
                            TrailingMinusNumbers:=True
        Set Rg = .Offset(0, 1)
    Next i
End With
End Sub

`For j = 1 To 2` and `Next i` ? this is most likely from another answer :) — Shai Rado, Feb 01 '17 at 11:23
@ShaiRado : My bad, it was a copy/paste from the 1st proposition! I corrected it, thx for pointing it out! ;) — R3uK, Feb 01 '17 at 11:24
Just another question, why do you need the `For` loop ? where so you use it ? — Shai Rado, Feb 01 '17 at 11:26
@ShaiRado : It's just to repeat the `.TextToColumns` twice, I thought it was the easiest/more readable way to put. If you have a better way, I'm all eyes! :) — R3uK, Feb 01 '17 at 11:28

Text to column for loop

1 Answers1