在今天和今天之间的VBA自动筛选-1年 [英] VBA Autofilter between todays date and todays date - 1 year

问题描述

我正在尝试在今天的日期和今天的日期减去1年之间进行过滤.当我运行下面的VBA时，什么也没有发生，并且没有设置过滤器.谁能指出我的正确方向，并且搜寻过很多高低的论坛.

I am trying to filter between todays date and todays date minus 1 year. When I run the below VBA nothing happens and the filter doesn't set. Can anyone point me in the correct direction and have searched forums high and low.

Sub Date_Filter()

    Keyboard Shortcut: Ctrl+Shift+Q

    Dim StartDate As Date
    Dim EndDate As Date
    StartDate = Date
    EndDate = Date - 365
    Sheets("Advisor Data").Select
     ActiveSheet.Range("$A$1:$AL$10000").AutoFilter Field:=4, Criteria1:=">=" & CDbl(EndDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(StartDate)

End Sub

推荐答案

一年有时有366天，因此，为了使代码更可靠，可以使用 DateSerial():

The year has sometimes 366 days, thus to make your code a bit robust you may use DateSerial():

Public Sub TestMe()

    Dim startDate As Date
    Dim endDate As Date
    startDate = Date
    endDate = DateSerial(Year(Date) - 1, Month(Date), Day(Date))

    Debug.Print endDate

End Sub

一旦您具有正确的 endDate ，自动过滤器就可以了.

Once you have the correct endDate, the autofilter looks ok.

这篇关于在今天和今天之间的VBA自动筛选-1年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！