Looping through cells in Range

Hi,

I can't tell why this is only looping through part of the range. I have tried resizing but still not working. The range is 5 * 10 and therefore the last x should be 50 but it only goes up to 10 making me think that the array is not two-dimensional. Any thoughts?

Please Login or Register  to view this content.

abousetta

Hi,

What is the last value in colum E? If you have blank cells at the bottom of the column E then you won't get all that (I think) you want.

Hi MarvinP,

No, I tested it with a mock data set and still doesn't work:

Please Login or Register  to view this content.

Its only counting up to 10.

abousetta

This is weird... The 10 being looped are actually Range("A1:E2")??? Why??? Even when I hard cody it as Range("A1:E10") it still gives me the same results.

From Help:

Syntax;
UBound(arrayname[, dimension])

If dimension is omitted, 1 is assumed. You have a two dim array of 10 rows X 5 columns, [cl : Variant/Variant(1 to 10, 1 to 5)] therefore UBOUND defaults to 10. But even if you had said: For x = LBound(cl,1) To UBound(cl,2), Ubound would have returned 5 since that's the number of columns. Did you want to use something like:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thanks Ben. That did the trick. Also now I understand my misconception about 2 dimensional arrays. What I should have done is:

Please Login or Register  to view this content.

That gave me the same results as:

Please Login or Register  to view this content.

I have a love/hate relationship with arrays... I love to try using them, but I hate that I keep on failing

Thanks again.

abousetta

Three methods to choose from:

Please Login or Register  to view this content.

Thanks snb. Method #1 and #3 are clear to me. Method #2 is a little bit more mysterious. Could you explain how this works:

Please Login or Register  to view this content.

abousetta

It loops through the whole range, using only on loop.
The whole array size is rows*columns
you can check using

Please Login or Register  to view this content.