在日期列中未定义过滤条件1和条件2 [英] Filter Criteria1 and Criteria 2 undefined on column of Dates

问题描述

根据Microsoft: https://docs.microsoft.com/en-us/office/vba/api/excel.filter.criteria2 可以设置一个变量= .Criteria2.这会在Excel 2010-2019/365中引发运行时1004错误.

According to Microsoft: https://docs.microsoft.com/en-us/office/vba/api/excel.filter.criteria2 It is possible to set a variable = .Criteria2. This throws a runtime 1004 error in Excel 2010-2019/365.

注意:如果过滤的列是日期，则Criteria1和Criteria2未定义:应用程序定义的错误或对象定义的错误.注意:当过滤一列文本或数字时，Excel将正确记住Criteria1.

Note: if the filtered column is dates, Criteria1 and Criteria2 are undefined: Application-Defined or Object-Defined Error. Note: Excel will correctly remember Criteria1 when filtering a column of text or numbers.

为简化审阅者的过程，我只记录了一个宏，该宏过滤了日期列并添加了注释以显示结果.

To simplify this for reviewers, I simply recorded a macro filtering a column of dates and added comments to show the results.

Sub Macro2()
'
' Macro2 Macro
'
Dim testfilter As Variant
Dim testfilter2 As Variant

' I recorded filtering Column A and removing a few dates

    ActiveSheet.Range("$A$1:$M$51").AutoFilter Field:=1, Criteria1:=Array("=") _
        , Operator:=xlFilterValues, Criteria2:=Array(0, "6/7/2021", 0, "10/27/2020", 0, _
        "11/16/2019", 1, "3/30/2018", 1, "5/17/2018", 1, "6/30/2018", 1, "7/28/2018", 1, _
        "9/3/2018", 1, "10/31/2018", 1, "12/29/2018")
    Set WS = ActiveSheet
    With WS.AutoFilter
       With .Filters(1)
         If .On And .Operator Then
            testfilter = .Criteria1 ' Criteria1 = "="
            testfilter2 = .Criteria2 ' Runtime Error 1004 - Application-Defined or object-defined error
        End If
       End With
    End With

End Sub

推荐答案

如果使用Excel的日期过滤器，则会定义Criteria1和Criteria2，并可以将其分配给变量，以便记住和重新应用它们.

If one uses Excel’s date filters, Criteria1 and Criteria2 are defined and can be assigned to a variable so that they can be remembered and reapplied.

如果某人的日期为2018年至2020年，并且在下拉过滤器菜单中取消选中2018年旁边的框，则Excel对Criteria2使用数组，但Criteria2仍未定义，并且如果尝试将其分配给变量将导致运行时错误1004

If one has dates from 2018 to 2020 and unchecks the box next to 2018 in the dropdown filter menu, Excel uses an array for Criteria2, but Criteria2 remains undefined and causes a Runtime Error 1004 if one tries to assign it to a variable.

解决方法:要求使用数据过滤器并使用代码来检测和处理未定义的Criteria1和Criteria2.

Workaround: Require use of data filters and use code to detect and handle undefined Criteria1 and Criteria2.

我要感谢ComputerVersteher的所有帮助.

I want to thank ComputerVersteher for all of the help.

这篇关于在日期列中未定义过滤条件1和条件2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！