Rick,
Thanks for your feedback. You've given me good guidance on how to move
ahead. I'll apply your concepts and see if it works out.
(Note: All the arrays have the same # of elements; I just typed ProdC
incorrectly.)

Thanks,
Stathy

"Rick Rothstein" wrote:

I'm still not 100% sure how you plan to put this idea to use; however, if I
understand your request correctly, there is a way to combine those existing
arrays into a multi-dimensional array... sort of.<g The following method of
producing a combined array will function the way you want, but the syntax
will look odd. Given these single arrays...

ProdA = Array("1/1/9", 3456, 85, widget, east)
ProdB = Array("1/1/9", 7689, 90, bolts, east)
ProdC = Array("7/1/9", 1245, 85, west)

I notice that the ProdC array has less elements than the other two arrays...
that is okay, this method will allow for that. You can make a
multi-dimensional array from them by doing this...

ComboArray = Array(ProdA, ProdB, ProdC)

Here is where the odd syntax comes in. You do NOT address an element like
this...

ComboArray(2, 1)

but, rather, you do it like this instead...

ComboArray(2)(1)

The indexing mechanism is the same an with a normal multi-dimensional array;
for example, in the above Combo(2)(1) reference, assuming the default Option
Base of 0 (so that array elements default to an initial index of 0), the 2nd
element (1245) of the 3rd array (ProdC) is being addressed. In other words,
the first number is parentheses is the index of the product arrays and the
second number is the index number of the element within that array. You can
find out the upper bound of the product arrays (ProdA, ProdB, etc.) with
this...

UBound(ComboArray)

so that the number of product arrays is UBound(ComboArray)-1, again,
assuming an Option Base of 0. Since each of the arrays that were combined
can possibly have different number of elements in them, you must check the
UBound for each one individually. For example, if you wanted to know the
upper bound of the ProdA array, you would find out using this...

UBound(ComboArray(0))

If you wanted to find the upper bound of the ProdC array, you would do
this...

UBound(ComboArray(2))

That is pretty much it... I sure hope all of the above is clear (if not, let
me know and I'll try to word the concept differently). I cannot think of any
other way to do what you appear to want to do within VB. Well, that is not
entirely true... you might be able to use a Collection to house your product
arrays, but I think the above array method may be more "robust".

--
Rick (MVP - Excel)


"Stathy K" wrote in message
...
I could do that with the current product line up, and it would work. But I
would think it would be easier to maintain single arrays as products are
added or retired. For example a new product would be (excuse punctuation
in
syntax),
ProdA=Array(1/1/09,3456,85,widget,east)
ProdB=Array(1/1/09,7689,90,bolts,east)

And then 6 mos later, I'd like to add/delete single arrays as product
lineup
changes:
(New) ProdC=Array(7/1/09,1245,85,west)
And I would only have to delete a line/array when a product is retired.

I would like it to remain as simple as possible, so that other users would
only have to enter a single dim array without needing any fancy VBA
experience.
After original post, I thought about a db but we do so much work in Excel
I'd rather stay native.

Thanks,
SK

"Rick Rothstein" wrote:

I don't think I understand what you are ultimately trying to do. From
what
you posted, I would ask why you don't just create the multi-dimensional
array right at the beginning and just use it for all your array needs?

--
Rick (MVP - Excel)


"Stathy K" wrote in message
...
I haven't seen this anywhere. I'd like to combine a group of single
arrays
to
a multi-dimension. The item array(s) have 5 characteristics (e.g.
A,B,C,D,E).
I'd like to make a new product family array that consists of all the
item
arrays
(e.g. A,B,C,D,E
F,G,H,I,E
A,G,H,J,K)
Then I could use the product family array as a look up table to extract
info. I'd like to get away from creating a worksheet to store the info,
since
it would only be temporary. Also, since new items are introduced every
6
months I can just add a new single item array, and then rebuild the
product
array.
Any ideas?
TIA,
SK