Excel VBA Iterate Array of Range Objects

Question

I am trying to iterate through an array of range references.

Dim labels As Collection
Set labels = New Collection
Dim myLabel As Range
Set myLabel = FindDataRow(mySearchterms, CurrentWorksheet) ' <- Returns a Range
labels.Add (myLabel)

Later I'm trying to iterate through those Range references and the following code does not work: I get a runtime error 424. I understand that an Object is required, but how did the label become a String.

Dim label As Variant
Set label = Nothing
For Each label In labels
    Debug.Print (label & " in: " & label.Row)
Next label

I have tried following this other answer here but since I'm new to VBA in Excel I do not understand how the Range reference is put in the data structure and later retrieved/referenced to access the label.Row as I want above (at the point where it says "~~> Do whatever you want to do with that range here")

My understanding so far is that labelsis a Collection and does not care about the type the items in it have. So I added Range references. But I can't retrieve them later. Where am I going wrong?

jkpieterse · Accepted Answer · 2016-01-28 13:43:58Z

1

Remove the brackets from the add method as that forces the value of the range to be added to the collection rather than the object:

labels.Add myLabel

answered Jan 28, 2016 at 13:43

jkpieterse

3,0491 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1532326 Over a year ago

Thank you! This took me the better part of a day.

Collectives™ on Stack Overflow

Excel VBA Iterate Array of Range Objects

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related