Filter a table for each row from another table

Question

What I need to do is filter through a table by using the second column from each row from another table. The table that I loop through contains 11 rows and the code loops 11 times, but it uses the 11th row each time. What it needs to do is go from the first row to the last row.

I have used this as source

Edit: With help of Variatus I have applied a count instead of For Each. This seems to be able to go through all the rows, except the last row. To be continued.

Edit2: for those who are curious, have a look at my follow up question where I got everything to work! Filter a table with an array of criteria derived from another table

Sub LoopDoorAfdelingV4()

Dim myTable As ListObject
Dim myTable2 As ListObject
Dim oRow As ListRow
Dim c As Long


Dim myGroupIDFilter As Variant
Dim myGroupNameFilter As Variant

Set myTable = ActiveSheet.ListObjects("TabelGroupID")
Set myGroupIDFilter = myTable.ListColumns(1).Range
Set myGroupNameFilter = myTable.ListColumns(2).Range
Set myTable2 = ActiveSheet.ListObjects("TabelAfdelingenIntern")


For c = 1 To myTable.ListRows.Count

ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=myGroupNameFilter(c), _
 Operator:=xlOr
Next c

End Sub

Answer 1

Consider setting up the filtering code with the maximum number of criteria you wish to be able to handle, like this (here demonstrated with just 3),

ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=myGroupNameFilter(1), _
Criteria2:=myGroupNameFilter(2),Criteria3:=myGroupNameFilter(3),Operator:=xlOr

Set the UBound for myGroupNameFilter to that same maximum number. Use the loop reading the criteria to fill the myGroupNameFilter array. Fill the elements of myGroupNameFilter for which you have no values with random values which you know can't exist in the list to be filtered, perhaps "XYZ987" or -99999. The filter should return the specified result because those criteria which find no match will not influence the result.

I hate to publish this code because it is sure not to work and I have no way of testing. It is intended to merely show the idea.

Dim Flt(1 To 15) As Variant

For i = 1 To 15
    If i > myTable.ListRows Then
        ' a value which doesn't exist in your Table2
        Flt(i) = "123XYZ"
    Else
        ' assign a value on which you intend to filter
        Flt(i) = myTable.ListRows(i).Cells(1).Value
    End If
Next

ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=Flt(1), _
                                                 Criteria2:=Flt(2), _
                                                 Criteria3:=Flt(3), _
                                                 Criteria4:=Flt(4), _
                                                 Criteria5:=Flt(5), _
                                                 Criteria6:=Flt(6), _
                                                 Criteria7:=Flt(7), _
                                                 Criteria8:=Flt(8), _
                                                 Criteria9:=Flt(9), _
                                                 Criteria10:=Flt(10), _
                                                 Criteria11:=Flt(11), _
                                                 Criteria12:=Flt(12), _
                                                 Criteria13:=Flt(13), _
                                                 Criteria14:=Flt(14), _
                                                 Criteria15:=Flt(15), _
                                        Operator:=xlOr

The point is that you can't set the filter in the loop in which you set the filter criteria.