8 thoughts on “Index Looping Through a Range”

1. My guess would be something like:

   Dim rArea as Range
   Dim rCel as Range

   For Each rArea in rAllfound.Areas
   For Each rCel in rArea.Cells
   ‘Do Something
   next rCel
   Next rArea

   (or use ‘For a = 1 to areas.count’ and ‘For c = 1 to cells.count’ if you REALLY like indicies)

2. Yes, there’s no easy way… you have to include the Areas in there. I came up with this function does seems to work:

   Sub Test()
   Dim Rng As Range
   Dim i As Long

   Set Rng = Range(“$A$2,$A$4:$A$5,$A$7?)

   For i = 1 To Rng.Count
   Debug.Print i, IndexArea(Rng, i).Address
   Next i
   End Sub

   Function IndexArea(ByVal Rng As Range, ByVal Index As Long) As Range
   Dim areaCounter As Long
   Dim i As Long

   i = 0
   Do
   i = i + 1
   areaCounter = areaCounter + Rng.Areas(i).Count
   Loop Until areaCounter > = Index

   If areaCounter = Index Then
   Set IndexArea = Rng.Areas(i).Item(Rng.Areas(i).Count)
   Else
   Set IndexArea = Rng.Areas(i).Item(areaCounter – Index)
   End If
   End Function

3. > ?rAllFound.Address
   > $A$2,$A$4:$A$5,$A$7
   >
   > There are four cells, as you can see.

   I think this concept is where you’re running into the problem. What you are listing there is NOT four cells, it’s three ranges (aka “areas”) that in total contain four cells. The UNION() function does just that — it creates a UNION of ranges — not a new range of individual cells. Hence the need to naviagate through the AREAS before the CELLS, as in:

   Set rAllFound = Application.Union(Range(“$A$2?), Range(“$A$4:$A$5?), Range(“$A$7?))
   For i1 = 1 To rAllFound.Areas.Count
   For i2 = 1 To rAllFound.Areas(i1).Cells.Count
   Debug.Print rAllFound.Areas(i1).Cells(i2).Address
   Next i2: Next i1

   Here’s an IMPORTANT note — the order you specify ranges in the UNION() statement may NOT be the same order they end up in as areas and cells. For example, suppose I have these three UNION() statements:

   Set rAllFound1 = Application.Union(Range(“$A$2?), Range(“$A$4:$A$5?), Range(“$A$7?))
   Set rAllFound2 = Application.Union(Range(“$A$2?), Range(“$A$4?), Range(“$A$7?), Range(“$A$5?))
   Set rAllFound3 = Application.Union(Range(“$A$2?), Range(“$A$7?), Range(“$A$4:$A$5?))

   Here are the addresses we end up with for each:

   rAllFound1.Address = $A$2,$A$4:$A$5,$A$7
   rAllFound2.Address = $A$2,$A$7,$A$4:$A$5
   rAllFound3.Address = $A$2,$A$7,$A$4:$A$5

   Note that on the rAllFound2 UNION(), the function automatically combined the $A$4 and $A$5 single-cell areas into a single area because they were adjacent. Which reiterates the point that we are not creating a new range — just a UNION() of ranges. If you add another range with $B$2 in it, the $A$2 range would move to join it.

4. First off, in the Immediate window in the VBE,

   ? Range(“C5?).Item(3).Address(0, 0)
   C7
   ? Range(“C5?).Cells(3).Address(0, 0)
   C7

   The Item and Cells properties don’t appear to be restricted to the cells in the Range.

   I don’t see how you avoid iterating through the range either using For Each or by area then by cell within area, so best to do it only once rather than repeatedly.

   Sub test()
   Dim r As Range, c As Collection
   Set r = Range(“A2,A4:A5,A7?)
   Set c = mkcoll(r)
   Debug.Print c(1).Address(0, 0)
   Debug.Print c(2).Address(0, 0)
   Debug.Print c(3).Address(0, 0)
   Debug.Print c(4).Address(0, 0)
   Debug.Print c(5).Address(0, 0)
   End Sub

   Function makecollection(x As Object) As Collection
   Dim nc As New Collection, v As Variant
   On Error GoTo Finish
   For Each v In x
   nc.Add Item:=v
   Next v
   Set makecollection = nc
   Finish:
   End Function

5. Why is it so important not to use For Each?

6. Jon: It’s not very important. In fact, IIRC, some MSKB article argues that For Each is more machine efficient. But, for most *practical* uses, it is less human efficient. In most cases where one wants to do something in sync with the control variable of a For Each loop, one is forced to implement a seperate index and manually sync it with the for each loop. Something like:
   Dim I as Integer, aRng as Range
   I=1
   For each aRng…
       …
       I=I+1
       next aRng

   Dick: Yeah, it’s a pain. In fact, I usually “black-box” such access with something similar — though a lot simpler — than Juan Pablo’s suggestion. I imagine he had a reason for his approach but it evades me.

   http://tmc.newsgrouphosting.com/readmessage?id=%3C3586ab9b$1236047a$ab0d@mps%3E&group=tmc.blogposts

7. For i = 1 to MyRange.Areas.Count
   For j = 1 to MyRange.Areas(i).Cells.Count
   ‘ blah blah blah
   Next j
   Next i

8. I was after similar/simple index access to cells within composite ranges.
   I found that COLLECTIONs of cells to replace the composite ranges allowed me to achieve this, viz;

   Private Function cCells(oRX As Range) As Collection
   Dim oCell As Range

   Set cCells = New Collection
   For Each oCell In oRX
   cCells.Add oCell
   Next oCell

   End Function

   ie my public functions called the cCells function…etc

Posting code? Use <pre> tags for VBA and <code> tags for inline.