从另一张表中为每一行过滤一张表 [英] Filter a table for each row from another table

问题描述

我需要做的是通过使用另一个表的每一行的第二列来筛选整个表。我循环访问的表包含11行，并且代码循环执行11次，但是每次都使用第11行。它需要做的是从第一行到最后一行。

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.

我已经使用过

编辑：在Variatus的帮助下，我应用了 count 而不是对于每个。除了最后一行，这似乎可以遍历所有行。 待续。

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

推荐答案

考虑使用以下命令设置过滤代码e您希望能够处理的最大条件数，像这样（此处仅用3个示例进行演示）

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

将 myGroupNameFilter 的UBound设置为相同的最大数字。使用循环读取条件来填充myGroupNameFilter数组。将您没有值的myGroupNameFilter元素填充为要过滤列表中可能不存在的随机值，例如 XYZ987或-99999。筛选器应返回指定的结果，因为找不到匹配项的条件将不会影响结果。

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.

这篇关于从另一张表中为每一行过滤一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！