Thread: 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?

Sub LoopThrougCells()

Dim x As Long
Dim Rng As Range
Dim cl

Set Rng = Range("A1:E" & Cells(Rows.Count, "A").End(xlUp).Row)
cl = Rng
    For x = LBound(cl) To UBound(cl)
        MsgBox x
    Next
End Sub

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:

1	1	1	1	1
2	2	2	2	2
3	3	3	3	3
4	4	4	4	4
5	5	5	5	5
6	6	6	6	6
7	7	7	7	7
8	8	8	8	8
9	9	9	9	9
10	10	10	10	10

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:

For Each x in cl
   Debug.Print x
Next x

or

For x=LBound(cl,1) to UBound(cl,1)
   For y=LBound(cl,2) to UBound(cl,2)
    ---
   next y
next x

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

    For x = LBound(cl, 1) To UBound(cl, 1)
        For y = LBound(cl, 2) To UBound(cl, 2)

That gave me the same results as:

For Each x in cl

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:

Sub snb()
 For Each cl In Cells(1).CurrentRegion
  MsgBox cl
 Next
    
 sn = Cells(1).CurrentRegion
 For j = 1 To UBound(sn) * UBound(sn, 2)
  MsgBox sn((j - 1) \ UBound(sn, 2) + 1, (j - 1) Mod UBound(sn, 2) + 1)
 Next

 sn = Cells(1).CurrentRegion
 For j = 1 To UBound(sn)
  For jj = 1 To UBound(sn, 2)
   MsgBox sn(j, jj)
  Next
 Next
End Sub

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

sn((j - 1) \ UBound(sn, 2) + 1, (j - 1) Mod UBound(sn, 2) + 1)

abousetta

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

msgbox "row: " & (j-1)\ubound(sn,2)+1 & vblf & "column: " & (j-1) mod ubound(sn,2) +1