Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Text to Columns looping VBA

  1. #1
    Join Date
    25th August 2004
    Posts
    4

    Text to Columns looping VBA

    I have several workbooks where I have a variable number of columns. I was wondering if anyone could tell me how to write a VBA code that would loop and convert each column to text until a column is blank? Especially frustrating is the VBA code that says

    Selection.TextToColumns Destination:=Range("B1")

    because then it'll paste all the columns it converts into cell B1.

    Many thanks in advance!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th August 2004
    Location
    Philly
    Posts
    17
    I'm not sure I understand your problem. Do you have formulas that you want to erase the formula and have only the result (ie cell a1 shows 2, but selecting that cell will let you see the formula "=1+1"). Or do you want to change numeric values to text values (a1 would become "2").

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676
    what's your delimiter ?
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

  4. #4
    Join Date
    25th August 2004
    Posts
    4

    Delimiter?

    There's no formulas. I'm not sure what a delimiter is.The reason I'm doing this is becuase someone else exports the files from Access to Excel which puts everything in text. The columns have text and numbers and the only way I can figure out to have the numbers as actual manipulatible (sp?) numbers is to use the "text to columns" function. However, I'd have to do this for 400 columns and was hoping to get around it by looping a macro.

    Here's the VBA I have but it keeps pasting the results in cell "B1".

    Columns("B:B").Select
    Do Until ActiveCell.Columns = " "
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    ActiveCell.Offset(0, 1).Columns.Select
    Loop

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th August 2004
    Posts
    4
    I almost figured it out!

    Columns("B:B").Select
    Do Until ActiveCell.Columns = " "
    Selection.TextToColumns Destination:=ActiveCell.Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Loop

    This VBA will select the column and then convert it until it reaches a blank column at which it gives me the error that there's no data to parse. Any thoughts?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th August 2004
    Location
    Philly
    Posts
    17
    Still not sure how to solve your overall problem, but try changing your
    Do Until ActiveCell.Columns = " "

    to

    Do Until ActiveCell.Columns = ""

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th August 2004
    Posts
    4
    Schweet! Here's the final VBA code incase anyone needs to convert a whole bunch of columns.

    Do
    Selection.TextToColumns Destination:=ActiveCell.Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Loop Until IsEmpty(ActiveCell.Columns("A:A"))

    Thanks everyone!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Rolling Text - Looping Screens
    By Ger Plante in forum EXCEL HELP
    Replies: 2
    Last Post: July 25th, 2007, 05:05
  2. Rolling Text - Looping Screens
    By Ktrasler in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: July 24th, 2007, 18:02
  3. Looping Columns
    By Keilcentre in forum EXCEL HELP
    Replies: 10
    Last Post: May 8th, 2007, 21:39
  4. Looping through columns to copy formulae
    By Horus Kol in forum EXCEL HELP
    Replies: 5
    Last Post: April 25th, 2006, 23:10
  5. Skiping text cells in a looping
    By Werner in forum EXCEL HELP
    Replies: 2
    Last Post: July 15th, 2005, 02:24

Bookmarks

Posting Permissions

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