Iterating through a new collection 2

[tyhand]

hi all,

i have four named ranges.
rangeA rangeB rangeC and rangeD (as Range)

each range has four cells with some data for
a total of 16 cells.

What i want to do is make a new collection
of the named ranges and iterate not just
through the members of the collection but
through each individual cell in each member range.
How can I accomplish this? So far what I
have keeps generating errors of 'object required'.

 

[RobBroekhuis]

Try to work with something like

Dim MyRange As Range, cell As Range
Set MyRange = Union(Range("RangeA&quot, Range("RangeB&quot, _
range("RangeC&quot, Range("RangeD&quot)
For Each cell In MyRange
..do stuff here..
Next cell

Rob

 

[tyhand]

Actually what I have so far is the following. However it keeps generating the error 'Object Required':

Dim rangeA, rangeB, rangeC, rangeD
Dim MyColl as New Collection, DaSet, r, c
'Set rangeA = whatever
'Set rangeB = whatever, etc...

MyColl.Add RangeA
MyColl.Add RangeB
MyColl.Add RangeC
MyColl.Add RangeD

For r = 1 To RangeColl.Count
DaSet = RangeColl(r)
For Each c In DaSet
If c.Value = Empty Then
Debug.Print "I'm Empty"
Else
Debug.Print "I'm Full"
End If
Next c
Next r

Possible cause of error?
Thanks in advance for the help.

 

[Zathras]

To answer your question (probable cause of error):

1. Inconsistent variable names: MyColl v. RangeColl
2. You need the "Set" operator in the line "DaSet =..."
as in "Set DaSet = ..."

To avoid errors like the first, use "Option Explicit" as the first line in your module. That way VBA would not be as "friendly" as allowing you to create a new variable with the name "RangeColl" on the fly.

But all this begs the question: Why do you think you need to use a Collection object? Rob's solution is the way to go - clean and neat.

 

[tyhand]

Thanks!

Caught the error. Will try Rob's solution as well.
Peace!