如何通过Cell VBA中的值类型过滤表

Question

我想根据第一个单元格在另一个单元格上过滤表

hi，在准备新报告期间，我遇到了这样的问题：我根据第一个单元格值尝试了另一个单元格上的过滤表。我编写的代码似乎有效，但显示出必要的结果，它仅突出显示空行。如果我提起使用字符串，请将日期直接放入代码中，一切都很好，但是可惜，这不是我想要的。有建议吗？

Sub Makro3()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim mySearch As Variant
    Dim sht As Worksheet
    Set ws = Arkusz6
    Set sht = Arkusz1
    Set tbl = ws.ListObjects("ineffective")
    'Set tbl = ws.Range("A4:E31")
    mySearch = sht.Range("B2").Value
    'tbl.Range.AutoFilter Field:=4, Criteria1:="28.06.2019", Operator:=xlAnd
    'tbl.Range.AutoFilter Field:=4, Criteria1:="=" & TextBox1.Text & "*", Operator:=xlAnd
    'tbl.Range.AutoFilter Field:=4, Criteria1:="*" & TextBox1.Text & "*", Operator:=xlFilterValue
    'tbl.Range.AutoFilter Field:=4, Criteria1:="=*" & mySearch & "*", Operator:=xlAnd
End Sub

Answer 1

这是一个示例，说明了如何根据单元格在另一张纸上的单元格值自动滤波器的日期值。在下面的示例中，日期在表的第2列中。

Sub FilterTable()
    Dim ws1 As Worksheet 'sheet with the table
    Dim ws2 As Worksheet 'sheet with the search parameter
    Dim tbl As ListObject 'table object
    Dim mySearch As Variant 'or Dim as Date; cell with the search parameter
    Set ws1 = Worksheets("Data")
    Set ws2 = Worksheets("Filter Table by Type")
    Set tbl = ws1.ListObjects("t_Data")
    mySearch = ws2.Range("A1").Value
    Debug.Print "mySearch = " & mySearch

    With tbl.Range
        '~~> Show all records
        .AutoFilter Field:=2
        If IsDate(mySearch) Then
            .AutoFilter Field:=2, _
            Operator:=xlFilterValues, _
            Criteria1:=mySearch
        Else
            .AutoFilter Field:=2, _
            Operator:=xlFilterValues, _
            Criteria1:=DateSerial(Year(mySearch), Month(mySearch), Day(mySearch))
        End If
    End With

    '~~> Release the variables from memory
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set tbl = Nothing
    mySearch = vbNull
End Sub