Convert text to columns for n number of rows in column A

[PocketNeo]

I am a big nOOb for VBA, so pardon my poor style below...I am here to learn. I need help on a basic macro to do a text to column coversion for 'n' number of rows with data that only exists in cell A1 to A'n'. I am having difficulty with the following macro...been playing with the part that that reads: Destination:=Range("A1"), such that "A1" becomes a variable that would allow me to increment after each text to column conversion. I have failed poorly...

Any ideas?


Sub MyTest()

' Variables
Row = 1
xROW = Cells(Row, 1)
Y = 1

' Work
Cells(Row, 1).Select

While Y > 0
Y = Len(xROW)

If Y > 0 Then
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(16, 2), Array(32, 2), Array(40, 2), _
Array(46, 2), Array(48, 2), Array(52, 2), Array(60, 2), Array(68, 2), Array(76, 2), Array( _
78, 2), Array(80, 2), Array(81, 2), Array(92, 2), Array(106, 2), Array(117, 2), Array(128, 2 _
), Array(139, 2), Array(153, 2), Array(156, 2), Array(159, 2), Array(170, 2), Array(181, 2) _
, Array(192, 2), Array(203, 2), Array(234, 2), Array(242, 2), Array(253, 2), Array(264, 2), _
Array(272, 2), Array(280, 2), Array(286, 2), Array(288, 2), Array(289, 2), Array(312, 9)) _
, TrailingMinusNumbers:=True
End If

Row = Row + 1
xROW = Cells(Row, 1)
Wend

End Sub

 

[Norie]

What range do you actually want to do the Text to Columns on?

Do you want to do it on column A, then column B or something like that?

Can you post an example of your data and required result, perhaps using Colo's HTML Maker?

 

[PocketNeo]

Q: What range do you actually want to do the Text to Columns on?
A: All data is in col A, specifically A1, A2, A3, etc. up to a variable amount of rows.

Q: Do you want to do it on column A, then column B or something like that?
A: I want to take 300 digits in cell A1 and parse into A1 through 35 separate columns (based on the array part of the macro in my post above), then take B1 and parse it into A1 to 35 columns, etc.

Here's an example in it's simplest form:
A1: 1234567890
A2: 0987654321

I want A1 to be broken down into a specific array set, similar to this style:

A B C
1 123 4567 890
2 098 7654 321

 

[PocketNeo]

bump

 

[PocketNeo]

I figured it out!

To make this work, create 3 cells of data (A1, A2, A3) with 32 characters per cell. You can have n number of rows...this is just for the sake of explaining this marco. Here's what I did...

Sub ReformatOneColToManyCols()
' Variables
Row = 1
Col = 2
Y = 1
xRow = Cells(Row, 1)
' Work
Cells(Row, Col).Select

While Y > 0

fieldX = Mid(xRow, 1, 8)
Cells(Row, Col) = fieldX
Col = Col + 1

fieldX = Mid(xRow, 9, 8)
Cells(Row, Col) = fieldX
Col = Col + 1

fieldX = Mid(xRow, 17, 16)
Cells(Row, Col) = fieldX
Col = Col + 1

' Increment the Row, set the next Row's length (xRow and Y), and reset the Col to 2 (we use col 2 since data exists in col 1.

Row = Row + 1 ' increment the row 1,2,3, etc.
xRow = Cells(Row, 1)
Y = Len(xRow) ' set the length of the row
Col = 2 ' reset column

Wend

' Delete COLUMN A (to make things clean and get rid of our original data)
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

' Insert a row and add headers (to make ti look pretty)
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "COL A Stuff"
Range("B1").Select
ActiveCell.FormulaR1C1 = "COL B Stuff"
Range("C1").Select
ActiveCell.FormulaR1C1 = "COL C Stuff"

Range("A1").Select ' return to cell A1

End Sub