Excel VBA Countif between dates

Question

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.

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

e.g. 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.

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

amd697 · Accepted Answer · 2014-07-16 00:20:56Z

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

If it's CDbl that does the trick then I have an explanation: matching functions compare things to a cell value's internal representation, Value2, instead of Value.

Collectives™ on Stack Overflow

Excel VBA Countif between dates

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related