array
 

the statement below takes the data in table 1 col A starting at A4 "03 DEC
50.000 (RIM TY)" and divides string into cols if a space is found.
a=3, b=DEC, c=50, d=(RIM, e=TY). the problem is it will ignore dividing col
A in the 2nd table that is below first table.
how would i modify the statement to work on data string in 2nd table
starting 7 lines below end of the date data string in first table.
the tables are divided by :
col A
03 DEC 50.000 (RIM TY) 'data end table 1
TOTAL

Last Price 37.54
Put
Series/
Strike Price
03 AUG 25.000 (RIM HC) 'data start table2
03 AUG 27.500 (RIM VX)




'break A into seperate cols
Range(Range("A4"), Range("A4").End(xlDown)).TextToColumns
Destination:=Range("A4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo
_
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1))

i am using excel 2k
thanks

array
 

"bob" wrote in message
. ca...
the statement below takes the data in table 1 col A starting at A4 "03
DEC
50.000 (RIM TY)" and divides string into cols if a space is found.
a=3, b=DEC, c=50, d=(RIM, e=TY). the problem is it will ignore dividing
col
A in the 2nd table that is below first table.
how would i modify the statement to work on data string in 2nd table
starting 7 lines below end of the date data string in first table.
the tables are divided by :
col A
03 DEC 50.000 (RIM TY) 'data end table 1
TOTAL

Last Price 37.54
Put
Series/
Strike Price
03 AUG 25.000 (RIM HC) 'data start table2
03 AUG 27.500 (RIM VX)




'break A into seperate cols
Range(Range("A4"), Range("A4").End(xlDown)).TextToColumns
Destination:=Range("A4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo
_
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1))

i am using excel 2k
thanks


Replace the range select with this, it will go to the last used range row.
Range("A4:A" &
Range(Worksheets("sheet1").UsedRange.Address).Rows .Count).TextToColumns