使用从另一个表派生的一组条件过滤表 [英] 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.

现在已解决此问题.下面的代码非常出色！

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

推荐答案

我推测这是由于您设置数组的方式所致，该数组将生成一个2D数组，而Criteria1:=myArray期望一个1D数组.

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

这篇关于使用从另一个表派生的一组条件过滤表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！