Results 1 to 3 of 3

Thread: VBA to Loop, TextToColumns

  1. #1

    VBA to Loop, TextToColumns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    After incorporating a few extra portions this is working EXACTLY how I needed, thank you so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •