如何修复日期过滤器VBA，因为它没有获取我范围内的所有日期 [英] How to fix date filter VBA as it is not picking up all dates that fall within my range

问题描述

我正在尝试创建一个过滤器，该过滤器将过滤掉我选择的日期内的所有日期.我选择的日期将始终反映一个月.

I'm trying to create a filter that will filter out all dates that fall within the dates I choose. The dates I choose will always reflect a whole month.

例如，如果我需要2019年5月的数据，我将输入开始日期为01/05/2019，结束日期为31/05/2019.

For example, if I need data for May 2019, I will input my start date as 01/05/2019 and my end date as 31/05/2019.

我的数据过滤器将需要提取开始日期之前的所有行.因此，它应该选择整个日历年(01/01/2019-31/12/2019)，季度(01/04/2019-30/06/2019)以及涵盖5月的任何日期.

My data filter will need to pick up all rows that go through my start date. So it should pick up whole calendar years (01/01/2019 - 31/12/2019), quarters (01/04/2019 - 30/06/2019) and any dates that cover the month of May.

当前，我的过滤器仅提取整个日历年和某些(但不是全部)确切的日期，例如01/05/2019-31/05/2019.因此，我错过了很多信息，例如，以季度为单位的日期或5月前后的数月之内.

Currently my filter only picks up whole calendar years and some (but not all)exact dates such as 01/05/2019 - 31/05/2019. So I'm missing a lot of information such as the dates that fall as quarters, or over a number of months surrounding May.

我试图通过移动小于和大于符号来过滤它的方式，但这只是意味着很多交易被排除在外了.

I've attempted to play around with the way that it filters by moving around the less than and more than sign but this just meant that a lot of the deals became excluded.

我在数据少得多的另一个工作簿上使用了此筛选器，并且它收集了所有数据.而在此工作簿上，有更多的数据，开始日期相差很大，所以我丢失了大约一半的数据.

I use this filter on another workbook with much less data and it picks up all data. Whereas on this workbook, there is much more data and the start dates vary a lot, so I lose about half the data I need.

Sub Filter()
    Dim lngStart As Long, lngEnd As Long
    lngStart = Range("AI1").Value 'this is the start date
    lngEnd = Range("AI2").Value 'this is the end date

    Range("G2:G5000").AutoFilter field:=7, _ 'this is the start date column
        Criteria1:="<=" & lngEnd, _
        Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd

    Range("H2:H5000").AutoFilter field:=8, _ 'this is the end date column
        Criteria1:=">=" & lngStart, _
        Operator:=xlAnd, _
        Criteria2:=">=" & lngStart


End Sub

当我单击过滤器时，我希望显示通过2019年5月(例如月份)的所有数据.它仅显示一个月的交易，而显示五个以上的交易，则显示四分之一的交易.

I would like all my data that passes through May 2019 (example month) to show when I click on my filter. It is only showing one of the month deals, and one of the quarter deals when there are five more.

推荐答案

日期在VBA过滤器中比较棘手.一方面，VBA日期数据类型与格式化为日期的Excel值不太相同.

Dates are tricky in VBA filters. For one thing, the VBA Date data type is not quite the same as an Excel value formatted as a date.

我认为您的逻辑也有些偏离.

And I think your logic was a bit off also.

这似乎可以处理您的示例数据.

This seems to work on your sample data.

Sub Filter()
    Dim lngStart As Long, lngEnd As Long, fltrRng As Range
    lngStart = Range("J1").Value 'this is the start date
    lngEnd = Range("J2").Value 'this is the end date
    Set fltrRng = Range("A2:H5000")


    With fltrRng
        .AutoFilter field:=7, _
        Criteria1:=">=" & CDbl(lngStart)

        .AutoFilter field:=8, _
       Criteria1:="<" & CDbl(lngEnd)
    End With

End Sub

请注意，我们定义了整个过滤器范围，但是我将以不同的方式动态地对其进行定义，以免包含不必要的行.

Note that we defined the entire filter range, but I would define it differently, and dynamically, so as not to encompass more rows than necessary.

With Worksheets("sheet1")
    Set fltrRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 8).End(xlUp))
End With

此外，我们将 lngStart 和 lngEnd 转换为Double数据类型，这是将日期存储在工作表上的Excel中的方式.您可能刚刚将它们声明为Double类型.因此，所有这些:

Also, we converted the lngStart and lngEnd to the Double data type, which is how dates are stored in Excel on the worksheet. You could have just declared them as being of type Double. So, with all that:

Sub Filter()
    Dim lngStart As Double, lngEnd As Double, fltrRng As Range
    lngStart = Range("J1").Value 'this is the start date
    lngEnd = Range("J2").Value 'this is the end date

    With Worksheets("sheet1")
        Set fltrRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 8).End(xlUp))
    End With

    With fltrRng
        .AutoFilter field:=7, _
        Criteria1:=">=" & lngStart

        .AutoFilter field:=8, _
       Criteria1:="<" & lngEnd
    End With

End Sub

最后，我认为逻辑应该表明您希望日期在

Finally, the logic should state, I believe, that you want dates where

• 开始列等于或大于开始日期 AND
• 结束"列小于结束日期(您输入的日期比实际期望的结束日期晚一个日期，这是适当的.

另一个问题是您的某些日期不是真实的Excel日期".换句话说，它们是日期的文本表示形式，其格式与您在月"项中输入> 12"的区域窗口设置不同.

Another problem is that some of your dates are not "real Excel dates". In other words, they are textual representations of dates in a format that is different from your regional windows settings, where the Month entry is > 12.

当打开文本文件或csv文件而不是IMPORT文件时，通常会出现此问题，并且文件中的日期格式与计算机的Windows区域设置中的日期格式不同.

This problem usually arises when a text or csv file is OPEN'd, rather than IMPORT'd, and the date format in the file is different from the date format in the Windows Regional Settings for the computer.

通过导入一个csv文件，可以指定传入数据的日期格式，从而避免此问题.

By IMPORTing a csv file, one can specify the date format of the incoming data and avoid this problem.

要在提供的文件中进行演示，请更改宏的过滤部分以同时选择文本日期.例如:

To demonstrate it in the file you provided, change the filtering part of the macro to also pick up text dates. eg:

With fltrRng
    .AutoFilter field:=7, _
    Criteria1:=">=" & lngStart, _
        Operator:=xlOr, _
    Criteria2:=Format(lngStart, """*""/mm/yyyy")

    .AutoFilter field:=8, _
   Criteria1:="<" & lngEnd, _
        Operator:=xlOr, _
    Criteria2:=Format(lngEnd - 1, """*""/mm/yyyy")

End With

但是，这远非理想，因为很可能甚至转换为真实Excel日期"的日期也可能被错误地转换.换句话说，如果CSV文件包含的 05/01/2019 表示 2019年1月5日，则它将转换为 2019年5月1日在引用的场景中.

However, this is far from ideal because, most likely, even the dates that are rendered as "real Excel dates" were probably translated incorrectly. In other words, if the CSV file contained 05/01/2019 meaning 5-Jan-2019, it would be translated to 1-May-2019 in the scenario cited.

这篇关于如何修复日期过滤器VBA，因为它没有获取我范围内的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！