passing FieldInfo value as a variable

[dannydy]

Hi i'm new here
i've been working on this code in excel marco...i've been worked on it almost a week, but still failed.
[vba]
Sub Macro2()
Public storage(1 To 100) As Integer
Dim i As Long
Dim FieldInfoVal As Variant
Dim DataStartCol As Integer
Dim rowselect As Integer
Dim colselect As Integer
Dim x As Long
colselect = 1
rowselect = 2
i = 1

'used array store the values, these values will pass to TextToColumns 'FieldInfo, if i hardcore the array, it will be like:
'array(1,9,3,12,10,3,6,1,14,15,15,10,10,155)
'the reason i used this method is that i will need change the value often according to my file format

Sheets("Sheet2").Activate
Do While Not IsEmpty(Cells(rowselect, colselect).Value)
storage(i) = Cells(rowselect, colselect).Value
DataStartCol = DataStartCol + 1
i = i + 1
rowselect = rowselect + 1
Loop

FieldInfoVal = ""
ReDim FieldInfoVal(1 To DataStartCol)

For x = 1 To DataStartCol
FieldInfoVal(x) = Array(storage(x), 2)
Next x

Sheets("Sheet1").Activate
Cells(3, 1).Select

'After executed this line, the result is not as my expected'
Selection.TextToColumns Destination:=Cells(3, 1), DataType:=xlFixedWidth, _
FieldInfo:=FieldInfoVal, TrailingMinusNumbers:= _
True

End Sub
[/vba]

Could anyone pls help me on this.
Many Thanks

[Bob Phillips]

Come on, we aren't mind-readers.

What is it supposed to do, on what data, and what actually happens.

[dannydy]

actaully this is what i wanted to covert my text to colums with Fixed width in the excel file.
[VBA]Sheets("Sheet1").Activate
Cells(3, 1).Select
Selection.TextToColumns Destination:=Cells(3, 1), DataType:=xlFixedWidth, _
FieldInfo:=array(array(1,2),array(9,2),array(3,2),array(12,2), array(10,2),array(3,2),array(6,2),array(1,2),array(14,2),array(15,2),array( 15,2),array(10,2),array(10,2),array(155,2) TrailingMinusNumbers:= _
True
[/VBA]
i wanna to make the FieldInfo:="variable"

This is what i've tried to do
[VBA]
Sheets("Sheet2").Activate
Do While Not IsEmpty(Cells(rowselect, colselect).Value)
storage(i) = Cells(rowselect, colselect).Value
DataStartCol = DataStartCol + 1
i = i + 1
rowselect = rowselect + 1
Loop
[/VBA]

as the value is reading from the sheet cells of each rows, this method allows me to change the value from the sheet rows. read the value from sheet cells and storage it into array.

[VBA]
FieldInfoVal = ""
ReDim FieldInfoVal(1 To DataStartCol)

For x = 1 To DataStartCol
FieldInfoVal(x) = Array(storage(x), 2)
Next x
[/VBA]

Retrieve the values from the storage(i) and store it into FieldInfoVal

[VBA]
Selection.TextToColumns Destination:=Cells(3, 1), DataType:=xlFixedWidth, _
FieldInfo:=FieldInfoVal, TrailingMinusNumbers:= _
True
[/VBA]

Perform convert text to coloumn. after converted, the result is
FieldInfo:=array(array(1,2),array(1,2),array(3,1),array(3,1),array(1,2),arr ay(4,1),array(1,2)
this is not as my expected
what i want to convert my data to columns.
FieldInfo:=array(array(1,2),array(9,2),array(3,2),array(12,2), array(10,2),array(3,2),array(6,2),array(1,2),array(14,2),array(15,2),array( 15,2),array(10,2),array(10,2),array(155,2)

i had found a lot of references and been trying to solve it. But still i was unble to solve it, i don't what had happended .

[mikerickson]

I note that from the macro recorder all these arrays are zero based,[vba]...FieldInfo:=array(array(1,2),array(9,2),...[/vba] but in the OP code, FieldInvorVal is a 1-based array.[VBA] ReDim FieldInfoVal(1 To DataStartCol)

For x = 1 To DataStartCol
FieldInfoVal(x) = Array(storage(x), 2)
Next x [/VBA]