Filtering Date based on cell value in Excel VBA

[primasatria]

Dear all,

I am using MS Office 365, I need to filter based on multiple criteria and based on date based on cell value in cell K1.
The problem, I tried by to run this code below, but somehow I can not filtered date in column K, while others can be filtered as I wanted.

Sub Filter_DailyReport()
    Dim fdate As Long
    Range("AK1").FormulaR1C1 = "=TODAY()-1"
    Range("AK1").Value = Range("AK1").Value
    'Range("AK1").NumberFormat = "[$-en-US]dd-mmm-yy;@"
    fdate = Range("AK1").Value
    
    Set ws = ThisWorkbook.Sheets("Database")
    ws.ListObjects("Table1").Range.AutoFilter Field:=28, Criteria1:="Approved"
    ws.ListObjects("Table1").Range.AutoFilter Field:=30, Criteria1:="Red"
    ws.ListObjects("Table1").Range.AutoFilter Field:=37, Criteria1:=fdate
    ws.ListObjects("Table1").Range.AutoFilter Field:=38, Criteria1:="FE Const."
    ws.ListObjects("Table1").Range.AutoFilter Field:=41, Criteria1:="JPK Utara"

    ' Define the range to apply the filter to
    'Set rng = ws.Range("G2:G" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row)

    ' Clear any existing filters
    'ws.AutoFilterMode = False
End Sub

Is there anybody could helping me? I've been stuck with this code for 3 days already.

Thank you so much, for your helping hand.

Best regards,
Prima - Indonesia

 

[Micron]

Try Dim fdate As Date. Date values in US format are floating point doubles, not longs. I don't see the point of this line:
Range("AK1").Value = Range("AK1").Value To me, that's like saying 10 = 10.
I suspect you're not using US date format, which is what Excel is based on regardless of your regional settings (as far as I know). I don't know if the NumberFormat property will work for you or not as I'm not familiar with it. If you do as suggested, make sure you test with ambiguous dates as well. An example of that is 02/11/2024. To me, that is February 11. To others, it is November 2nd.

Also, this is a way to concatenate date values as US format in Access so maybe it would work for Excel also:
Format(fdate, "\#mm\/dd\/yyyy") & "#;"
You might need to declare fdate as a Variant to use that.

 

[primasatria]

Try Dim fdate As Date. Date values in US format are floating point doubles, not longs. I don't see the point of this line:
Range("AK1").Value = Range("AK1").Value To me, that's like saying 10 = 10.
I suspect you're not using US date format, which is what Excel is based on regardless of your regional settings (as far as I know). I don't know if the NumberFormat property will work for you or not as I'm not familiar with it. If you do as suggested, make sure you test with ambiguous dates as well. An example of that is 02/11/2024. To me, that is February 11. To others, it is November 2nd.

Also, this is a way to concatenate date values as US format in Access so maybe it would work for Excel also:
Format(fdate, "\#mm\/dd\/yyyy") & "#;"
You might need to declare fdate as a Variant to use that.

Dear Micron,

Thanks for helping me.
Finally, based on your suggestion I changed fdate as Variant as below.
Tested several times with different date and it works.
Problem solved!

Sub Filter_DailyReport()
    'Dim fdate As Date
    Dim fdate As Variant
    Range("AK1").FormulaR1C1 = "=TODAY()-1"
    Range("AK1").Value = Range("AK1").Value
    fdate = Range("AK1").Value
    
    Set ws = ThisWorkbook.Sheets("Database")
    ws.ListObjects("Table1").Range.AutoFilter Field:=28, Criteria1:="Approved"
    ws.ListObjects("Table1").Range.AutoFilter Field:=30, Criteria1:="Red"
    'ws.ListObjects("Table1").Range.AutoFilter Field:=37, Criteria1:=fdate
    ws.ListObjects("Table1").Range.AutoFilter Field:=37, Operator:= _
        xlFilterValues, Criteria2:=Array(2, fdate)
    ws.ListObjects("Table1").Range.AutoFilter Field:=38, Criteria1:="FE Const."
    ws.ListObjects("Table1").Range.AutoFilter Field:=41, Criteria1:="JPK Utara"


    ' Clear any existing filters
    'ws.AutoFilterMode = False
End Sub

Thank you very much.
Really appreciate it.

Dear MrExcel,
Thank you very much

Regards,
prima - Indonesia

 

[Micron]

Glad I could help in some small way! Thanks for the recognition.