PDA

View Full Version : passing FieldInfo value as a variable



dannydy
06-01-2010, 08:47 PM
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.:banghead:

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


Could anyone pls help me on this.
Many Thanks

Bob Phillips
06-02-2010, 12:47 AM
Come on, we aren't mind-readers.

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

dannydy
06-02-2010, 01:48 AM
actaully this is what i wanted to covert my text to colums with Fixed width in the excel file.
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

i wanna to make the FieldInfo:="variable"

This is what i've tried to do

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


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.


FieldInfoVal = ""
ReDim FieldInfoVal(1 To DataStartCol)

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


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


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


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:dunno .

mikerickson
06-02-2010, 05:57 AM
I note that from the macro recorder all these arrays are zero based,...FieldInfo:=array(array(1,2),array(9,2),... but in the OP code, FieldInvorVal is a 1-based array. ReDim FieldInfoVal(1 To DataStartCol)

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