日期之间的Excel VBA Countif
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24644714/
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
Excel VBA Countif between dates
提问by amd697
I am needing to create a function to count the occurrences of an account number between two dates. The first date is based on the function input and the second is 3 months in advance (date may not be contained within the data set). Date values in the range are in the format "dd/mm/yyyy h:mm". Due to the size of the dataset approx 150,000 lines i am wanting to perform this in the code and not paste or evaluate the COUNTIF formula within a specified cell.
我需要创建一个函数来计算两个日期之间帐号的出现次数。第一个日期基于函数输入，第二个是提前 3 个月（日期可能不包含在数据集中）。该范围内的日期值采用“dd/mm/yyyy h:mm”格式。由于数据集的大小约为 150,000 行，我想在代码中执行此操作，而不是在指定单元格中粘贴或评估 COUNTIF 公式。
The worksheet function works when only the AccountNo variable is referenced but not when the conditional ">=" or "<=" Date variables are added
当仅引用 AccountNo 变量时，工作表函数起作用，但在添加条件 ">=" 或 "<=" 日期变量时不起作用
e.g. Application.WorksheetFunction.CountIfs(Range("L2:L" & Endrow), AccountNo) > 1 Then ''''(Works)
例如 Application.WorksheetFunction.CountIfs(Range("L2:L" & Endrow), AccountNo) > 1 Then ''''(Works)
The function needs to return a result based on the countif result as below.
该函数需要根据 countif 结果返回一个结果，如下所示。
Thanks,
谢谢，
Function LastWrapUp(Date1 As Date, AccountNo)
Dim Date2 As Date
Dim Endrow As Long
Date2 = DateAdd("M", 3, Date1)
Endrow = Range("A" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIfs(Range("A2:A17643"), ">=" & Date1, Range("A2:A" & Endrow), "<" & Date2, Range("L2:L" & Endrow), AccountNo) > 1 Then
LastWrapUp = "Not Final Wrap Up"
ElseIf Application.WorksheetFunction.CountIfs(Range("A2:A" & Endrow), ">=" & Date1, Range("A2:A" & Endrow), "<" & Date2, Range("L2:L" & Endrow), AccountNo) = 1 Then
LastWrapUp = "Yes"
Else
LastWrapUp = "Error"
End If
Debug.Print LastWrapUp
End Function
回答by amd697
For those who may come across this and are interested, the solution is to add Cdbl and Cdate functions around the Date1 and Date 2 Variables (changed to Newdate and AccountDate variables for clarity as per the below). Works like a charm. Working with date formats in VBA can be a pain!
对于那些可能遇到这个问题并且感兴趣的人，解决方案是在 Date1 和 Date 2 变量周围添加 Cdbl 和 Cdate 函数（为了清楚起见，如下所示更改为 Newdate 和 AccountDate 变量）。奇迹般有效。在 VBA 中使用日期格式可能会很痛苦！
Function LastWrapUp(AccountID, AccountType, AccountDate As Date)
'Find if current WrapUp is the last for a given account number within a 3 month period.
'need to include reference to specific sheets
Dim NewDate As Date
Dim LastRow As Long
NewDate = DateAdd("M", 3, AccountDate)
LastRow = Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A" & Rows.Count).End(xlUp).Row
If AccountType = "Dummy ID" Then
LastWrapUp = "Dummy ID"
ElseIf Application.WorksheetFunction.CountIfs(Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), ">=" & CDbl(CDate(AccountDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), "<" & CDbl(CDate(NewDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("L2:L" & LastRow), AccountID) > 1 Then
LastWrapUp = "Not Final Wrap Up"
ElseIf Application.WorksheetFunction.CountIfs(Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), ">=" & CDbl(CDate(AccountDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), "<" & CDbl(CDate(NewDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("L2:L" & LastRow), AccountID) = 1 Then
LastWrapUp = Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range(AccountID.Address).Offset(0, -4)
Else
LastWrapUp = "Error"
End If
End Function
