Excel Arrays

MirageSF used Ask the Experts™

I know very little about arrays in Excel,

Basically I have sets of data, 400 records, each containing 10 separate rows and 12 columns of data.

If I use say myArray(400,10,12) as variant

Do I then just use myArray=range("A1:Z400") < but cover the 400x10x12 cells?

Then how do I individual reference all these ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Specify the array without dimensions, Dim myArray() As Variant
Load the data into the array, myArray = Range("A1:Z400")
Then the array has 400 rows and 26 columns.
Arrays loaded from worksheet has base 1, so it works like using the cells(row,column) method.
It always has 2 dimensions, even if it is only one column.
To get a value from row 10, column 3 use X = myArray(10,3)
Or insert a value into the array, myArray(10,3) = X
To return the array back to the sheet use Range("A1:Z400") = myArray
NorieAnalyst Assistant

What do you want to do with the data sets?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial