0

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?

Community
  • 1
  • 1
user1532326
  • 13
  • 3

1 Answers1

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
jkpieterse
  • 2,727
  • 1
  • 9
  • 18