Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Text to Columns looping VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    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").

    Comment


    • #3
      what's your delimiter ?
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #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

        Comment


        • #5
          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?

          Comment


          • #6
            Still not sure how to solve your overall problem, but try changing your
            Do Until ActiveCell.Columns = " "

            to

            Do Until ActiveCell.Columns = ""

            Comment


            • #7
              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!

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X