VBA to Loop, TextToColumns

zer0sum

New member
Joined
Feb 18, 2012
Messages
2
Reaction score
0
Points
0
I am looking to create a VBA script that will take everything pasted in a sheet and perform a loop with TextToColumns for each column until it reaches "B:B". (Let's say the loop will start at column AZ, and works itself to the left.) It has to use "Space" and OtherChar:="/". I have attached a copy of what I am looking for. Preferably, if possible, I'd also like it to through a sum at the bottom of the columns, the tables are made of a day's 30 minute intervals, so the rows will never change, however I may need to include more columns. (A whole month or more's worth.)

I am not too familiar with incorporating this type of code, so if you could show me exactly where it fits in, I'd really appreciate it.

Thanks for at least looking at this thread!

View attachment Book1.xlsx

Code:
Sub TextToColumns()


Selection.TextToColumns _
        Destination:=Cells(Col.Row, Col.Column), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, _
        Space:=True, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=True, _
        Other:=True, OtherChar:="/"


End Sub
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Try this out:

Code:
Sub Macro1()
Dim col As Long
    With ActiveSheet
        For col = .Range("A1").End(xlToRight).Column To 2 Step -1
            .Columns(col).TextToColumns DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
                "/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
            .Columns(col).Insert
        Next col
        .Columns(col).Delete
    End With
End Sub
 

zer0sum

New member
Joined
Feb 18, 2012
Messages
2
Reaction score
0
Points
0
After incorporating a few extra portions this is working EXACTLY how I needed, thank you so much!
 
Top