How do you return the object index within a collection. If I have a
collection of ranges and I want to loop thru the collection using a For Each
loop. What property do I track for each rng to obtain the collection number?
Or do I have to use a Counter variable?

Set colRanges = New Collection

For Each rng2 In colRanges

Next

Thanks
EM

Perhaps this...

Not sure if this is what you want...

Sub test()
Dim colRanges As Collection
Dim rng As Range
Dim lng As Long

Set colRanges = New Collection
With colRanges
.Add Range("A1:A10")
.Add Range("B1:B10")
.Add Range("C1:C10")
End With

For Each rng In colRanges
MsgBox rng.Address
Next rng

For lng = 1 To colRanges.Count
MsgBox colRanges(lng).Address
Next lng

End Sub
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

> How do you return the object index within a collection. If I have a
> collection of ranges and I want to loop thru the collection using a For Each
> loop. What property do I track for each rng to obtain the collection number?
> Or do I have to use a Counter variable?
>
> Set colRanges = New Collection
>
> For Each rng2 In colRanges
>
> Next
>
> Thanks
> EM
>
>

One way:

Dim colRanges As Collection
Dim i As Long

Set colRanges = New Collection
colRanges.Add Range("A1:A10")
colRanges.Add Range("B1:B10")
colRanges.Add Range("C1:C10")
For i = 1 To colRanges.Count
If colRanges.Item(i).Address(False, False) = "B1:B10" Then _
MsgBox i
Next i


In article <69F81935-1E10-4E77-BE01-(E-Mail Removed)>,
ExcelMonkey <(E-Mail Removed)> wrote:

> How do you return the object index within a collection. If I have a
> collection of ranges and I want to loop thru the collection using a For Each
> loop. What property do I track for each rng to obtain the collection number?
> Or do I have to use a Counter variable?
>
> Set colRanges = New Collection
>
> For Each rng2 In colRanges
>
> Next
>
> Thanks
> EM

Just curious. Is there any advantage to using

colRanges.Item(i)
over
colRanges(i)

I use the .item when I have the object as part of a with but otherwise I
tend to omit it.

with colRanges
.item(i)
end with

--
HTH...

Jim Thomlinson


"JE McGimpsey" wrote:

> One way:
>
> Dim colRanges As Collection
> Dim i As Long
>
> Set colRanges = New Collection
> colRanges.Add Range("A1:A10")
> colRanges.Add Range("B1:B10")
> colRanges.Add Range("C1:C10")
> For i = 1 To colRanges.Count
> If colRanges.Item(i).Address(False, False) = "B1:B10" Then _
> MsgBox i
> Next i
>
>
> In article <69F81935-1E10-4E77-BE01-(E-Mail Removed)>,
> ExcelMonkey <(E-Mail Removed)> wrote:
>
> > How do you return the object index within a collection. If I have a
> > collection of ranges and I want to loop thru the collection using a For Each
> > loop. What property do I track for each rng to obtain the collection number?
> > Or do I have to use a Counter variable?
> >
> > Set colRanges = New Collection
> >
> > For Each rng2 In colRanges
> >
> > Next
> >
> > Thanks
> > EM
>

I generally prefer to be explicit.

AFAIK, it compiles the same, so it doesn't matter one way or the other
for efficiency.

However, for my own efficiency in reading code I wrote six months ago,
the more explicit I was, the better.

And for anyone who tries to maintain my code and doesn't use the "col"
convention, it's immediately obvious that the variable refers to a
collection rather than an array.

Just my US$0.02.


In article <85E9AEE2-6A05-47C4-900B-(E-Mail Removed)>,
Jim Thomlinson <James_Thomlinson@owfg-Re-Move-This-.com> wrote:

> Just curious. Is there any advantage to using
>
> colRanges.Item(i)
> over
> colRanges(i)
>
> I use the .item when I have the object as part of a with but otherwise I
> tend to omit it.
>
> with colRanges
> .item(i)
> end with