Iterating over a collection (VBA)

Question

Hello I am new to VBA and I am trying to iterate over a collection i've made and execute an action for each value.

Here is my code:

Sub makeRowsMatch()

Dim rows As VBA.Collection
Set rows = New VBA.Collection

Dim i As Integer
Dim j As Integer
Dim y As Integer


For i = 2 To 22203
For j = 2 To 121

If Cells(i, 2).Value <> Cells(j, 38) Then
     rows.Add (i)

End If

Next j
Next i


For Each y As Object in rows
rows(y).Delete

Next y

End Sub

I keep on getting an error in the line:

For Each y As Object in rows

It keeps on getting highlighted yellow, but when I remove the above line is get the following error:

For Each control variable must be Variant or Object

Could someone explain why it is not letting me iterate over the values? The whole issue of declaring variables is new to me. Thanks!

Answer 1

Declare y as Variant, i.e.:

Dim y As Variant

and change your loop like this:

For Each y In rows

Then the loop will work or, at least, it won't give you an error at that point.

But the next problem/error is inside the loop. This line:

rows(y).Delete

will give you an error "Object required", or something like that, because rows(y) return an Integer, so you can't call Delete on it. There's some confusion here because rows on its own means the Rows range of the active sheet. But you also named your collection as rows, so there's a naming conflict.

Rename your rows to, for example, myrows and it should then all work. However, I don't know what you're trying to do with the Delete line, so I can't advise further.

Answer 2

You've got a conflict in your declarations and use. This line declares y as an Integer, clearly:

Dim y As Integer  

But you then try to use it as an Object, either with

For Each y As Object in rows  

or

For Each y in rows

Change the declaration to

Dim y As Object

(I'm not that familiar with VBA. If the above doesn't work, change the declaration to Dim y As Variant instead.)