Text To column Loop

[zawawi]

Hi all,

Im trying to figure out how to loop below codes. I may have more than 60 list of materials though. appreciate if anyone can help.

'Material 1
Range("A2").Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(16, 1), Array(58, 1), Array(65, 1)), _
TrailingMinusNumbers:=True
Range("A3").Select
Selection.TextToColumns Destination:=Range("F2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(25, 1), Array(37, 1), Array(40, 1), _
Array(43, 1), Array(54, 1), Array(64, 1), Array(73, 1)), TrailingMinusNumbers:=True
Range("A4").Select
Selection.TextToColumns Destination:=Range("O2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(26, 1), Array(63, 1)), TrailingMinusNumbers _
:=True




'Material 2
Range("A5").Select
Selection.TextToColumns Destination:=Range("B5"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(16, 1), Array(58, 1), Array(65, 1)), _
TrailingMinusNumbers:=True
Range("A6").Select
Selection.TextToColumns Destination:=Range("F5"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(25, 1), Array(37, 1), Array(40, 1), _
Array(43, 1), Array(54, 1), Array(64, 1), Array(73, 1)), TrailingMinusNumbers:=True
Range("A7").Select
Selection.TextToColumns Destination:=Range("O5"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(26, 1), Array(63, 1)), TrailingMinusNumbers _
:=True


'Material 3
Range("A8").Select
Selection.TextToColumns Destination:=Range("B8"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(16, 1), Array(58, 1), Array(65, 1)), _
TrailingMinusNumbers:=True
Range("A9").Select
Selection.TextToColumns Destination:=Range("F8"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(25, 1), Array(37, 1), Array(40, 1), _
Array(43, 1), Array(54, 1), Array(64, 1), Array(73, 1)), TrailingMinusNumbers:=True
Range("A10").Select
Selection.TextToColumns Destination:=Range("O8"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(26, 1), Array(63, 1)), TrailingMinusNumbers _
:=True

 

[zawawi]

Hi all,

I finally managed to get it to work :D i just started learning VBA a month ago and it can really be mind blowing but alot of fun. heres the code i used to loop this. though i am not sure how efficient my codes are or if theres any better way to do it. i did this based on a few research from the net. but someone suggested another way to do this, im gona try it out and see what i can learn from there

Sub Anotherloopcodes2()


Dim LastRowA As Long
Dim i As Long
Dim j As Long
Dim p As Long


    


LastRowA = Range("A" & rows.Count).End(xlUp).Row
   


    
    'Looping first row starting Range A2
    For i = 2 To LastRowA Step 3 
        Cells(i, 1).TextToColumns Destination:=Cells(i, 2), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(16, 1), Array(58, 1), Array(65, 1)), _
        TrailingMinusNumbers:=True
    Next i
  
    'Looping second row Range A3
    For j = 3 To LastRowA Step 3
    Cells(j, 1).TextToColumns Destination:=Cells(j - 1, 6), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(25, 1), Array(37, 1), Array(40, 1), _
        Array(43, 1), Array(54, 1), Array(64, 1), Array(73, 1)), TrailingMinusNumbers:=True
    Next j


    'Looping third row Range A4
    For p = 4 To LastRowA Step 3
    Cells(p, 1).TextToColumns Destination:=Cells(p - 2, 15), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 9), Array(26, 1), Array(63, 1)), TrailingMinusNumbers _
        :=True
    Next p




End Sub