Excel VBA之间的日期之间 [英] Excel VBA Countif between dates

问题描述

我需要创建一个函数来计算两个日期之间的帐号的发生。第一个日期是基于函数输入，第二个是提前3个月（日期可能不包含在数据集中）。范围内的日期值格式为dd / mm / yyyy h：mm。由于数据集的大小约为15万行，我希望在代码中执行此操作，而不是粘贴或评估指定单元格中的COUNTIF公式。

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.

工作表函数仅当AccountNo变量被引用时才起作用，但是当添加条件> =或< =日期变量时才可以使用

The worksheet function works when only the AccountNo variable is referenced but not when the conditional ">=" or "<=" Date variables are added

Application.WorksheetFunction.CountIfs（Range（L2：L& Endrow），AccountNo）> 1 Then''''（Works）

e.g. Application.WorksheetFunction.CountIfs(Range("L2:L" & Endrow), AccountNo) > 1 Then ''''(Works)

该函数需要返回基于countif结果的结果如下。

The function needs to return a result based on the countif result as below.

谢谢，

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

推荐答案

对于那些可能遇到这个和有兴趣的人，解决方案是在Date1和Date 2变量周围添加Cdbl和Cdate函数（如下所示，为了清楚起见，更改为Newdate和AccountDate变量）。奇迹般有效。在VBA中使用日期格式可能会很痛苦！

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!

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

这篇关于Excel VBA之间的日期之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！