动态条件-筛选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 Stringcrit1 = ?

因此:

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！