VBA - 将自动筛选应用于所有工作表，但在不同的列中

Question

我需要对工作簿中的 4 张工作表应用格式和过滤器 - 对于前 3 张工作表，格式和过滤器需要应用于 B 列和 C 列中的最后一张工作表。

所以我想这样做 - 我搜索列标题，如果找到，将格式应用于该列，然后应用过滤器。

法典：

Sub DateFilter()
'Initialize variables
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim c As Range
Dim LastRow As Long
Dim Current_Date As Date
Dim y As Workbook
Dim WshtNames As Variant, WshtNameCrnt As Variant
Set y = Workbooks("workbook.xlsm")
For Each ws In y.Worksheets
With ws
bFound = False
'Search for the Word "Status" on the first row of the Sheet
Set StatusFound = ws.Rows(1).Find(What:="Employee End Date", LookAt:=xlWhole)
'If Status is found then apply filter
If Not StatusFound Is Nothing Then
For a = .UsedRange.Columns.Count To 2 Step -1
If .Cells(1, a).Value = "Employee End Date" Then
'Loop through all celss in column B and change format to date
'For Each c In ActiveSheet.Range("C2:C" & LastRow).Cells
Current_Date = CDate(a)
a.Value = Current_Date
'Next c

'Make all cells in column A "General" input
Columns(1).NumberFormat = "General"
' Set the autofilter to display all dates other than yesterdays
ws.UsedRange.AutoFilter Field:=1, Criteria1:="<" & CLng(DateAdd("d", -1, Date)), Operator:=xlOr, Criteria2:=">" & CLng(DateAdd("d", -1, Date))
bFound = True
End If
Next a
End If
End With
Next
End Sub

但是在这一行a.Value = Current_Date我也得到了一个对象定义的错误。

注意：在最后一个示例中，我已经注释掉了 If 语句中的 for 循环，因为我认为这种情况没有必要。

Answer 1

你的第一次尝试非常接近你想要的。只需清理一下，然后根据需要使用第二个宏多次调用它。

• 在范围引用中明确。Columns(1).NumberFormat = "General"是在活动工作表上而不是在ws上操作的。只 在开头添加一个句点，因为您在With语句中：.Columns(1).NumberFormat = "General".
一次
• 对一个单元格的大范围数据进行操作更多 如果将数据作为变体数组拉入，则高效执行 操作，然后将结果输出为数组。

这是我的刺痛：

Option Explicit
Public Sub DateFilterAll()
With Workbooks("workbook.xlsm")
DateFilter .Sheets("Sheet1"), 2
DateFilter .Sheets("Sheet2"), 2
DateFilter .Sheets("Sheet3"), 2
DateFilter .Sheets("Sheet4"), 3
End With
End Sub
Public Sub DateFilter(ws As Worksheet, iCol As Integer)
'Initialize variables
Dim rng As Range
Dim lngLastRow As Long
Dim lngCurrRow As Long
Dim vDates() As Variant
With ws
'Find the last row with contents
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
'get the values from column iCol
Set rng = .Range(.Cells(2, iCol), .Cells(lngLastRow, iCol))
vDates = rng.Value
'Loop through the values from column iCol and convert to date
For lngCurrRow = 1 To lngLastRow - 1
vDates(lngCurrRow, 1) = CDate(vDates(lngCurrRow, 1))
Next lngCurrRow
'output the date values
rng = vDates
'Make all cells in column A "General" input
.Columns(1).NumberFormat = "General"
' Set the autofilter to display all dates other than yesterdays
.Columns(iCol).AutoFilter Field:=1, Criteria1:="<>" & CLng(DateAdd("d", -1, Date))
End With
End Sub