我想用 Criteria1 的當前值填充當前作業表的自動過濾范圍的欄位 #2 的字串變數“x”。
我想我正在尋找一個簡化版本，但我無法弄清楚如何簡化它。
過濾后的表總是在第 10 行有標題，資料在 A:Z 列中，我只關心 B 列（欄位 #2）的條件 1 和條件 2 值。過濾條件將始終是字串（不是陣列或日期）。
Sub CycleFilterSettingsFieldTWO()
Dim x As String
x= ActiveSheet.AutoFilter.field(2).criteria1 'best guess, doesn't work!
'I've also tried ...
'activesheet.autofilter.filters.filterarray(10,2)
'activesheet.autofilter.filters.item(1,1)
Select Case x
Case Is = "3"
ActiveSheet.AutoFilter.Range.AutoFilter field:=2, Criteria1:="4"
Case Is = "4"
ActiveSheet.AutoFilter.Range.AutoFilter field:=2, Criteria1:="*" 'Show everything
Case Else
ActiveSheet.AutoFilter.Range.AutoFilter field:=2, Criteria1:="3"
End Select
End Sub
uj5u.com熱心網友回復：
回圈自動過濾器
短的
x = ActiveSheet.AutoFilter.Filters(2).Criteria1
例子
Sub CycleFilterSettingsFieldTWO()
Const Col As Long = 2
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
If ws.AutoFilterMode Then
Dim rg As Range: Set rg = ws.AutoFilter.Range
If ws.FilterMode Then
Select Case ws.AutoFilter.Filters(Col).Criteria1
Case "=3"
rg.AutoFilter Col, "4"
Case "=4"
ws.ShowAllData
Case Else
ws.ShowAllData
End Select
Else
rg.AutoFilter Col, "3"
End If
End If
End Sub
這是一個如何回圈過濾器的示例。假設AutoFilterMode是True。
Sub LoopFiltersExample()
Dim fs As Filters: Set fs = Sheet1.AutoFilter.Filters
Dim f As Filter
Dim n As Long
For Each f In fs
n = n 1
If f.On Then
Debug.Print n, f.Criteria1
Else
Debug.Print n, "Is off"
End If
Next f
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/529612.html
標籤：擅长vba自动过滤器