源自单元格引用的 vba 日期过滤条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20223344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba date filter criteria derived from a cell reference
提问by user3037345
I'm trying to filter data where one filter criteria - month - needs to be entered by the user in a cell on one sheet in the workbook.
我正在尝试过滤数据，其中一个过滤条件 - 月份 - 需要由用户在工作簿的一张工作表上的单元格中输入。
If the data is typed into the code, it works fine, as written below:
如果将数据输入到代码中，它就可以正常工作，如下所示：
ActiveSheet.Range("$A:$P73").AutoFilter Field:=13, Criteria1:=Array( _
"="), Operator:=xlFilterValues, Criteria2:=Array(1, "10/1/2013")
ActiveSheet.Range("$A:$P73").AutoFilter Field:=12, Criteria1:="<>"
ActiveSheet.Range("$A:$P73").AutoFilter Field:=3, Criteria1:="<>"
The column to be filtered has dates displayed as MM-YYYYbut show in the formula bar as DD-MM-YYYY. I haven't been able to replace the date 10/1/2013with a variable, cell ref or named range linked to another sheet.
要过滤的列的日期显示为，MM-YYYY但在公式栏中显示为DD-MM-YYYY。我无法10/1/2013用链接到另一个工作表的变量、单元格引用或命名范围替换日期。
So, data to be filtered is in Sheet1and the month is entered into a cell in Sheet2.
因此，要过滤的数据在 中Sheet1，月份被输入到 中的单元格中Sheet2。
Can someone help with this?
有人可以帮忙吗？
回答by Takedasama
I don't use VBA to filter my tables but following your example, I believe that you must first:
我不使用 VBA 来过滤我的表格，但按照您的示例，我相信您必须首先：
a). adjust the code to use 2 criterias when filtering. Thus you should capture dates between 1st and 31st. Your 1st criteria seams to be mixed up.
一种）。调整代码以在过滤时使用 2 个标准。因此，您应该捕获 1 日和 31 日之间的日期。你的第一个标准接缝被混淆了。
ActiveSheet.Range("$A:$P73").AutoFilter Field:=13, Criteria1:="<1/10/2013" _
, Operator:=xlAnd, Criteria2:=">30-10-2013"
b). use the mm-yyyy input and build the 2 values that define the timeframe. Do this by adding 1 and 31 to create a valid dates. Then declare the variable:
b)。使用 mm-yyyy 输入并构建定义时间范围的 2 个值。通过添加 1 和 31 来创建有效日期。然后声明变量：
MyMonth = Sheets("Sheet2").Range("A1").Value 'Change A1 to the cell used to store the month
MyYear = Sheets("Sheet2").Range("A2").Value 'Change A2 to the cell used to store the year
Month_1st = DateSerial(MyYear, MyMonth , 1)
Month_31st = DateSerial(MyYear, MyMonth , 31) 'fine-tuning needed to sort the months ending 30th
' use these in the code above like this:
Criteria1 = "<" & Month_1st
Criteria2 = ">" & Month_31st
ActiveSheet.Range("$A:$P73").AutoFilter Field:=13, Criteria1:=Criteria1 _
, Operator:=xlAnd, Criteria2:=Criteria2
Haven't tested this all the way but I think it should work. I may recommend another approach if the given code is not of use. Cheers!
还没有完全测试过，但我认为它应该可以工作。如果给定的代码没有用，我可能会推荐另一种方法。干杯!
