data from a sheet to a matrix
 

Dear Friends,

how can save data from an excel sheet into a two dimensional array i
order to perform for ... next i operations?
Out of your experience is it memory wise better to store data in V
variables and work with them or to work directly with the spreadshee
with Range("").Select etc. commands?

Additionally, is there a way to speed up the vlookup operation?

Thank you in advance

--
Message posted from http://www.ExcelForum.com

data from a sheet to a matrix
 

banavas < wrote:

Dear Friends,

how can save data from an excel sheet into a two dimensional array in
order to perform for ... next i operations?
Out of your experience is it memory wise better to store data in VB
variables and work with them or to work directly with the spreadsheet
with Range("").Select etc. commands?

Additionally, is there a way to speed up the vlookup operation?

Thank you in advance.


---
Message posted from http://www.ExcelForum.com/

Not sure what you mean by "memory wise", but it's certainly likely to be
faster using arrays.

Alan Beban

data from a sheet to a matrix
 

Dim vArr as Variant

vArr = Range("A1:B4")
for i = lbound(varr,1) to ubound(varr,1)
for j = lbound(varr,2) to ubound(varr,2)
msgbox "vArr(" & i & ", " & j & ") = " & varr(i,j)
next
Next


Note that you must pick up the values with a variant variable. In excel
2000 and later you can declare

Dim vArr() as Variant

In xl97 and earlier, you can not use the () in the declaration.

If it is a single column, you can use application.Transpose to get a one
dimensional array

vArr = Application.Transpose( Range("A1").Resize(10,1).Value)
for i = lbound(varr) to ubound(varr)
msgbox i & ", " & varr(i)
Next

If it is a single row, use Transpose twice. Otherwise both will come in a a
2D array with one dimension 1 to 1. Arrays built this way always have a
lower bound of 1.

Vlookup seems pretty fast. You could try passing an array as the second
argument.

Whether to loop a range or grab it in an array usually depends on what you
want to do.

--
Regards,
Tom Ogilvy


"banavas " wrote in message
...
Dear Friends,

how can save data from an excel sheet into a two dimensional array in
order to perform for ... next i operations?
Out of your experience is it memory wise better to store data in VB
variables and work with them or to work directly with the spreadsheet
with Range("").Select etc. commands?

Additionally, is there a way to speed up the vlookup operation?

Thank you in advance.


---
Message posted from http://www.ExcelForum.com/