Thanks for the continued education on arrays. Sometime I actually

feel like I am clear up to about the third grade with arrays.<g>

Regarding Dimming as Long. I always use Long now since the new row

numbers in Excel are a million.

Long is the default TYPE returned/expected when getting/setting the

index of a row/col. It's been my observation that many people assign

Type Integer to vars used for refs to row/col positions/counts or loop

counters.

Is the Long with the counters any different than what I have in my

first post?

Nope!

As to using Index, I came across a discussion among MVP's using

Index. Seemed to be something kinda new..?

I keep this example as a cheater guide, but don't understand the two

1's. I typed =Index(... on the sheet and there are great similarities

as you would expect, but the little syntax window couldn't 'splain it

well enough to me.

- myRng, 1, 1,

There are 2 forms of the Index function: Array and Reference!

The assignment to 'vArr' in the *MyArryCellsRange()* example is

incorrect when using the 'Array' form of the Index function because it

only accepts 3 args: array(), row&, col&) as defined in the Function

Reference...

INDEX Uses an index to choose a value from a reference or array

Array form:

Returns the value of an element in a table or an array (array: Used to

build single formulas that produce multiple results or that operate on

a group of arguments that are arranged in rows and columns. An array

range shares a common formula; an array constant is a group of

constants used as an argument.), selected by the row and column number

indexes.

Use the array form if the first argument to INDEX is an array constant.

Syntax:

INDEX(array,row_num,column_num)

...where 'array' can be either a range of cells or an array constant.

Your sample code *does correctly example* using the 'Reference' form of

the Index function, though, because it assigns MyArray 'areas' (a

non-contiguous range)...

Reference form:

Returns the reference of the cell at the intersection of a particular

row and column. If the reference is made up of nonadjacent

selections,

you can pick the selection to look in.

Syntax:

INDEX(reference,row_num,column_num,area_num)

...where 'reference' is a ref to one or more cell ranges.

If you are entering a non-adjacent range for 'reference', enclose

'reference' in parentheses.

If each area in 'reference' contains only one row or column, the

row_num or column_num argument, respectively, is optional. For

example, for a single row reference, use

INDEX(reference,,column_num).

...and 'row_num' is the number of the row in 'reference' from which to

return a ref.

...and 'column_num' is the number of the column in 'reference' from

which to return a ref.

...and 'area_num' selects a range in reference from which to return the

intersection of row_num and column_num.

The first area selected or entered is numbered 1, the second is 2,

and so on. If 'area_num' is omitted, INDEX uses area 1.

For example, if 'reference' describes the cells (A1:B4,D1:E4,G1:H4),

then 'area_num' 1 is the range A1:B4, 'area_num' 2 is the range

D1:E4,

and 'area_num' 3 is the range G1:H4.

The above info is snipped from the help page. Read the entire help for

this function for further understanding. *Note* that this doesn't work

on 1D arrays!

--

Garry

Free usenet access at

http://www.eternal-september.org
Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion