CODEWITHSUNDEEP
arraysexcelvbaloopsfilter
Joey b
Joey b

Reputation: 25

Filter a table with an array of criteria derived from another table

In another question I figured out how to filter a table with for each row from another table. Now I need to filter again another table with an array of criteria from the filtered table.

When I use the code I tagged here underneath, I recieve an error; Type mismatch.

Ultimately I need to print every result of this loop.

Edit: This question is now solved. The code underneath is working brialliantly!

    Sub LoopDoorAfdelingV4()

Dim myTable As ListObject
Dim myTable2 As ListObject
Dim c As Long
Dim myArray As Variant


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 = 2 To myTable.ListRows.Count

ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=myGroupNameFilter(c)

Set myfilteredgroup = myTable2.ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeVisible)

With Application
    myArray = .Transpose(myfilteredgroup)
End With


Worksheets("Vorige werkdag").Range("$E$2:$P$10000").AutoFilter Field:=5, Criteria1:=myArray, _
Operator:=xlFilterValues

Worksheets("Vorige werkdag").PrintOut Copies:=1, Collate:=True, _
       IgnorePrintAreas:=False

Next c

End Sub

Upvotes: 0

Views: 744

Answers (1)

Tom
Tom

Reputation: 9878

I'd speculate that this is due to the way you've set the Array will generate a 2D array and the Criteria1:=myArray is expecting a 1D array.

If your data is in a column you can use

With Application
    myArray = .Transpose(myfilteredgroup.SpecialCells(xlCellTypeVisible))
End With

and if it is a row use

With Application
    myArray = .Transpose(.Transpose(myfilteredgroup.SpecialCells(xlCellTypeVisible)))
End With

Upvotes: 1

Related Questions