MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Addressing arrays and their elements


Posted by Ken Brading on November 16, 2000 9:33 AM

I have three arrays that are each 5 rows X 4 columns,
their names are Select1, Select2, and Select3. Previous
calculations determine which array I am to take
elements from for subsequent calculations. The previous
calcs compute a 1, 2, or 3. I'm trying
to concat Select&i for the array name and having no luck.

Ideally, I would like to reference the three arrays in
a manner similar to Fortran:
Select&i(1,1) through Select&i(5,4)

There are basically two questions here,
1) How can I concat "Select" and a number and have it
be intrepreted as an array name when I copy the
array somewhere else?
2) Is it possible to access elements of an array with
double subscripted variables (something like it)?

thank you,
Ken Brading


Posted by Tim Francis-Wright on November 16, 2000 9:48 AM

You could set up a 3-dimensional array:-
Dim Select(3,5,4)

That way Select1 could be Select(1,x,y);
Select2 could be Select(2,x,y); and
Select3 could be Select(3,x,y).

HTH

Posted by Ken Brading on November 16, 2000 10:23 AM


How do I accomplish this on a worksheet?
I'm not using VBA macros for this.
Ken

Posted by Tim Francis-Wright on November 16, 2000 10:58 AM

I think I have a workaround for you:
Define Select1 as the top left of the range
of cells in the two-dimensional range of
Select1 cells, and do the same for Select2
and Select3.

Then the formula
=OFFSET(INDIRECT("Select"&i),c-1,r-1)
will give the value in row r and column c
of Selecti (where i = 1, 2, or 3).

Posted by Ken Brading on November 16, 2000 11:07 AM

Thank you very much! I have a different work
around in mind, but the INDIRECT command is
what I needed. Once again thank you.
Ken