I want to move through the cells in a range using an index number (like in a For Next loop) without using a For Each statement. I have a range that is the Union of all the cells identified using the Find method. The variable rAllFound has the address:
$A$2,$A$4:$A$5,$A$7
There are four cells, as you can see. Once this range is created, I set my variable lCurrent to 1 and that allows me to work with the first cell in the range.
$A$2
Now I want to increment/decrement lCurrent to use the next cell. I want this:
$A$4
I get this:
$A$3
These characteristics of the Range object aren’t surprising, but that doesn’t get me any closer to my goal. I could loop through rAllFound with a For Each and identify the cell I want, but I didn’t like that solution.
You’ll see in my next post that I ended up using FindNext and FindPrevious, which works just fine, but I just can’t believe there’s no way to navigate a non-contiguous range using only an index. I’m sure I’m missing something simple and obvious and I’ll appreciate it if you can set me straight.
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)
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
> ?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.
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
Why is it so important not to use For Each?
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
For i = 1 to MyRange.Areas.Count
For j = 1 to MyRange.Areas(i).Cells.Count
‘ blah blah blah
Next j
Next i
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