4

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
Community
  • 1
  • 1
amd697
  • 133
  • 1
  • 2
  • 7

1 Answers1

7

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
amd697
  • 133
  • 1
  • 2
  • 7
  • 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`](https://stackoverflow.com/a/44993861/648265). – ivan_pozdeev Sep 01 '17 at 13:23