Range objects in a collection

[Corey B]

Hello,

I've been trying to store a number of ranges in a collection, but
instead of storing the range it seems to store the cells value. The
purpose here is to add to the collection as needed and at the end
print out the contents with workbook, worksheet and cell reference for
each item.

Dim initRange as Range
Set initRange = worksheets("sheet1").Range("$A$1") ' contents = 48

Dim myCollection as New Collection
myCollection.Add(initRange)

Dim tempRange as Range
Set tempRange = myCollection.Item(1) 'fails with object required error

Any suggestions, or am I stuck using an array of ranges?

Thanks,
Corey

 

[Bob Kilmer]

On myCollection.Add(initRange), lose the ()'s. These are causing initRange
to be evaluated before the add.

Or add Call,

Call myCollection.Add(initRange), whch requires ()'s to enclose the
argument.

 

[Dave Peterson]

Remove the () from this line:
myCollection.Add(initRange)

And it worked ok for me.