动态条件-筛选Excel VBA [英] Dynamic Criteria - Filtering Excel VBA

问题描述

我需要根据相同的条件进行过滤，但是在接收到的数据中，条件的值并不总是相同的，因此它们必须是动态的.

I need to filter on the same criteria, but the values of the criteria are not always the same in the data that I receive, so they need to be dynamic.

例如.

将crit1昏暗为字符串 crit1 =吗?

Dim crit1 as String crit1 = ?

因此:

Selection.AutoFilter字段:= 4，条件1:= crit1

Selection.AutoFilter Field:=4, Criteria1:=crit1

这是我的代码

enter code herexDim TaskType, Status, Elapse As Long
Dim Total, Completed As Variant
Total = Array("COMPLETED", "ERROR", "KILLED")
Completed = Array("COMPLETED")
TaskType = WorksheetFunction.Match("tasktypeid", Rows("1:1"), 0)
Status = WorksheetFunction.Match("status", Rows("1:1"), 0)
Elapse = WorksheetFunction.Match("elapse", Rows("1:1"), 0)
'Use Filter Criteria
'100 Total
With Sheets("Raw_Data")
Set rnData = .UsedRange
With rnData
.AutoFilter field:=TaskType, Criteria1:="100"
.AutoFilter field:=Status, Criteria1:=Total, Operator:=xlFilterValues
.Select
        For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
        lcount = lcount + rngarea.Rows.Count
        Next
        a = lcount - 1
   End With
      End With
'100 Completed
    With Sheets("Data")
    Set rnData = .UsedRange
    With rnData
    .AutoFilter field:=TaskType, Criteria1:="100"
    .AutoFilter field:=Status, Criteria1:=Completed, Operator:=xlFilterValues
    .Select
            For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
            lcount1 = lcount1 + rngarea.Rows.Count
            Next
            b = lcount1 - 1
       End With
          End With

'101 Total
    With Sheets("Raw_Data")
    Set rnData = .UsedRange
    With rnData
    .AutoFilter field:=TaskType, Criteria1:="101"
    .AutoFilter field:=Status, Criteria1:=Total, Operator:=xlFilterValues
    .Select
            For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
            lcount2 = lcount2 + rngarea.Rows.Count
            Next
            c = lcount2 - 1
       End With
          End With
'101 Completed
    With Sheets("Data")
    Set rnData = .UsedRange
    With rnData
    .AutoFilter field:=TaskType, Criteria1:="101"
    .AutoFilter field:=Status, Criteria1:=Completed, Operator:=xlFilterValues
    .Select
            For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
            lcount3 = lcount3 + rngarea.Rows.Count
            Next
            d = lcount3 - 1
       End With
          End With

在上面的代码中，我静态计算出standard1:=为100,101，但是它应该采用过滤器中存在的动态值. 预先感谢.

In above code i have calculated criteria1:= as 100,101 statically but it should take dynamic value present in the filter. Thanks in advance.

推荐答案

例如， 使用我的宏记录器为包含"a"的项目过滤"I"列，我将得到这种代码.

For example, Using my macro recorder to filter Column "I" for items that contain "a", I would get this kind of code.

Sub Macro5()
'
' Macro5 Macro
'

'
    Columns("I:I").Select
    Selection.AutoFilter
    ActiveSheet.Range("$I$1:$I$7").AutoFilter Field:=1, Criteria1:="=*a*", _
                                              Operator:=xlAnd
End Sub

现在，我了解了代码如何使用通配符，现在我可以编辑代码以使用通配符和变量. 我的新代码看起来像这样.

Now I see how the code uses wildcards and I can now edit the code to use wildcards and my variable. My New code would look like this.

Sub FilterForA()
    Dim s As String
    s = "a"
    Columns("I:I").AutoFilter Field:=1, Criteria1:="=*" & s & "*"
End Sub

这篇关于动态条件-筛选Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！