时间戳

首页 > 解决方案 > 如何使用日期过滤数据

excel - 如何使用日期过滤数据

问题描述

我试图根据输入到输入框中的季度过滤掉日期，但它一直抛出错误。我对vba有点陌生，还在学习。任何帮助将不胜感激。谢谢。到目前为止，这是我想出的。

ResponseYr = SdrYear
ResponseQtr = SdrQtr

If ResponseQtr = 1 Then
BeginDate = "1 / 1 /" & ResponseYr & "0:00"
BeginDate = DateSerial(ResponseYr, 1, 1)
EndDate = "31 / 3 /" & ResponseYr & "0:00"
EndDate = DateSerial(ResponseYr, 3, 31)
'Quarter1 = Range("BeginDate:EndDate")


ElseIf ResponseQtr = 2 Then
BeginDate = "1 / 4 /" & ResponseYr & "0:00"
BeginDate = DateSerial(ResponseYr, 4, 1)
EndDate = "30 / 6 /" & ResponseYr & "0:00"
EndDate = DateSerial(ResponseYr, 6, 30)
'Quarter2 = Range("BeginDate:EndDate")

ElseIf ResponseQtr = 3 Then
BeginDate = "1 / 7 /" & ResponseYr & "0:00"
BeginDate = DateSerial(ResponseYr, 7, 1)
EndDate = "30 / 9 /" & ResponseYr & "0:00"
EndDate = DateSerial(ResponseYr, 9, 30)
'Quarter3 = Range("BeginDate:EndDate")

ElseIf ResponseQtr = 4 Then
BeginDate = "1/10/" & ResponseYr & "0:00"
BeginDate = DateSerial(ResponseYr, 10, 1)
EndDate = "31 / 12 /" & ResponseYr & "0:00"
EndDate = DateSerial(ResponseYr, 12, 31)
'Quarter4 = Range("BeginDate:EndDate")
End If
With ActiveSheet.Range("A:AD")
'.AutoFilterMode = False
.AutoFilter Field:=15, Criteria1:=Array("Accept", "Alteration Change Commencement Date", "Alteration 
Reinstatement", "Alteration Manual", "Term Expire", "Claim Repudiated", "Claim Payment Done"), 
Operator:=xlFilterValues
.AutoFilter Field:=13, Criteria1:=">=BeginDate", Operator:=xlAnd, Criteria2:="<=EndDate"
 End With

标签： excelvba

解决方案

要完全回答您的问题，了解错误发生的位置会很有帮助。要过滤所需的日期，您必须分隔字符串和变量：

.AutoFilter Field:=13, Criteria1:=">=" & BeginDate, Operator:=xlAnd,  Criteria2:="<=" & EndDate

但是在 excel vba 中过滤日期可能会很棘手。您可能还必须将日期转换为数字：

 .AutoFilter Field:=13, Criteria1:=">=" & CDbl(CDate(BeginDate)), Operator:=xlAnd,  Criteria2:="<=" & CDbl(CDate(EndDate))

推荐阅读