VBA自动滤波器按日期设置为Excel-未显示任何数据

Question

下面的代码是我为该项目的完整代码的摘要，但这就是失败的地方。用户键入将设置为自动滤波器的单元C3中的日期。但是，当我执行此操作时，自动滤波器不会返回数据（即使确实存在）。我已经看到了整个论坛上的查询，并尝试了这些问题提供的许多建议，但仍然无法显示蚂蚁数据。任何人都可以提供一些方向。Excel中的日期为" DD/MM/YYYY"

Option Explicit
Sub autofilter_by_date()
Dim wks As Worksheet
Set wks = ActiveSheet
Sheets("Log").Select
Dim dDate As Date
dDate = Worksheets("Sheet1").Range("C3")
dDate = DateSerial(Year(dDate), Month(dDate), Day(dDate))
With wks
    If Not .AutoFilterMode Then
Range("Table2[[#Headers],[Date Requested]]").AutoFilter
    End If
If .FilterMode Then .ShowAllData
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Operator:= _
    xlFilterValues, Criteria1:=dDate
Range("Table2[[#Headers],[H Name]]").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End With
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes
    Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
End Sub

Answer 1

尝试更改：

1。

来自

Dim dDate As Date
dDate = CDate(Format(Worksheets("Controls").Range("C3"), "dd/mm/yyyy"))

to

Dim dDate As String
dDate = Format(Worksheets("Sheet1").Range("C3"), "dd/mm/yyyy")

2。

来自

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria1:=dDate

to

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=">=" & CLng(dDate), Operator:=xlAnd, Criteria2:="<" & CLng(dDate) + 1

Answer 2

下面的完整代码，尝试和测试。（Excel表中的单元格是一个数字显示的日期）

Option Explicit
Sub autofilter_by_date()
Dim wks As Worksheet
Set wks = ActiveSheet
Sheets("Log").Select
Dim dDate As String
dDate = Format(Worksheets("Controls").Range("C4"))

With wks
    If Not .AutoFilterMode Then
Range("Table2[[#Headers],[Header Name]]").AutoFilter
    End If
If .FilterMode Then .ShowAllData
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=">=" & CLng(dDate), _
                 Operator:=xlAnd, Criteria2:="<" & CLng(dDate) + 1
Range("Table2[[#Headers],[Haulier Name]]").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End With
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
End Sub