Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Matrix Data | Excel Discussion (Misc queries) | |||
Extracting data from a product matrix | Excel Discussion (Misc queries) | |||
Manipulating a matrix of data | Excel Discussion (Misc queries) | |||
Plot data from column to matrix | Excel Discussion (Misc queries) | |||
Create a matrix from data in three column | Excel Discussion (Misc queries) |