vba - Excel VBA - 自动过滤一组日期的日期列

Question

我仍在学习 Excel VBA，如果有人能帮助我解决这个问题，我将不胜感激。我想根据我在名为 DateMaster 的工作表中指定的开始日期和结束日期来过滤日期列。当我运行宏时，它给出运行时错误 13，开始日期 = 0。下面是我的代码。

Sub FIlterCopy()

Dim StartDate As Long
Dim EndDate As Long

StartDate = ThisWorkbook.Worksheets("DateMaster").Range("C2").Value
EndDate = ThisWorkbook.Worksheets("DateMaster").Range("D2").Value

Application.ScreenUpdating = False

ThisWorkbook.Worksheets("FilterMaster").Activate

Range("A:BA").Select

Selection.ClearContents

Application.Workbooks.Open ("C:\WRI\Data\Revenue Update.xls")

Windows("Revenue Update.xls").Activate

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:= _
"=Backlog", Operator:=xlOr, Criteria2:="=RMA"
Selection.AutoFilter Field:=29, Criteria1:= _
"=Direct"
Selection.AutoFilter Field:=20, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

Application.ScreenUpdating = True

End Sub

Answer 1

如果您将StartDate和EndDate设置为Long，则应在自动过滤时使用CDate命令

Selection.AutoFilter Field:=20, Criteria1:=">=" & CDate(StartDate), Operator:=xlAnd, Criteria2:="<=" & CDate(EndDate)

否则，将两个变量定义为Date。