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