I have a UDF called CountPeriod which is a glorified version of CountIf. The workbook is used to tally student attendance. There is one tab per month with a linear calendar at the top of the page. To the Right, I need to tally the absences and tardies for each student based on which of 3 Periods/Terms that particular month is in. These monthly tallies are all totaled on a finally summary sheet. The Start and End Dates are named ranges on a separate sheet. Based on these dates, the UDF determines what range to include in the CountIf Function. The function is working as needed except that it returns a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] error unless forced to recalculate via Ctrl+Shft+Alt+F9. This Workbook is being designed for an excel novice so I need to figure out how to get this straightened out. The [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] error is only coming in where PeriodRange (in the code below) Is Not Nothing. In essence, where I should get a returned not-Zero value, I get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] . Like I said, when I force the calculation, I get the correct result.
Also, There are approximately 900 cells per sheet with this formula and calculation is running slow. If there are any recommendations on speeding this process, that would be appreciated.
Code:
Function CountPeriod(StartDate As Range, EndDate As Range, DateRange As Range, _
CountRange As Range, CountWhat1 As Variant, Optional CountWhat2 As Variant, _
Optional CountWhat3 As Variant) As Long
Dim FirstDate As Range
Dim LastDate As Range
Dim MatchED As Range
Dim MatchSD As Range
Dim PeriodRange As Range
Dim m As Long
Dim R As Long
If DateRange.Rows.Count > 1 Then
MsgBox "DateRange must be a singular row"
End If
Set FirstDate = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(1).Column) 'First day of selected DateRange
With DateRange 'Find last date in DateRange
Set LastDate = .Find("*", After:=.Cells(1), _
LookIn:=xlValues, searchorder:=xlByColumns, Searchdirection:=xlPrevious)
End With
'Find where the Period starts and ends in relation to the selected DateRange
Select Case StartDate.Value
Case Is < FirstDate.Value 'Period begins before the selected DateRange begins
Select Case EndDate.Value
Case Is > FirstDate.Value 'Period Ends after selected DateRange begins
Select Case EndDate.Value
Case Is > LastDate.Value 'Period starts before and ends after selected DateRange
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
Case Is < LastDate.Value 'Period begins before daterange starts, but ends before DateRange ends
m = Application.Match(CLng(CDate(EndDate.Value)), DateRange, 0)
Set MatchED = Cells(DateRange.Row, DateRange.Columns(m).Column)
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, MatchED.Column))
Case Is = LastDate.Value 'Period begins before daterange starts and ends on last day of DateRange
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
End Select
Case Is < FirstDate.Value 'Period Ends before selected daterange begins
CountPeriod = 0
Case Is = FirstDate.Value 'Period ends on first day of Daterange
Set PeriodRange = DateRange.Worksheet.Cells(CountRange.Row, FirstDate.Column)
End Select
Case Is > FirstDate.Value 'Period begins after the selected DateRange begins
Select Case StartDate.Value
Case Is > LastDate.Value 'Period begins after daterange ends
CountPeriod = 0
Case Is < LastDate.Value 'Period begins after start, but before end of DateRange
m = Application.Match(CLng(CDate(StartDate.Value)), DateRange, 0)
Set MatchSD = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(m).Column)
Select Case EndDate.Value
Case Is < LastDate.Value 'Period ends before the DateRange Ends - Period is contained entirely within daterange
m = Application.Match(CLng(CDate(LastDate.Value)), DateRange, 0)
Set MatchED = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(m).Column)
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, MatchED.Column))
Case Is > LastDate.Value 'period ends after DateRange Ends
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, LastDate.Column))
Case Is = LastDate.Value 'Period ends on last day of date range
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, LastDate.Column))
End Select
Case Is = LastDate.Value 'Period begins on last day of DateRange
Set PeriodRange = DateRange.Worksheet.Cells(CountRange.Row, LastDate.Column)
End Select
Case Is = FirstDate.Value 'Period begins on the first day of selected DateRange
Select Case EndDate.Value
Case Is < LastDate.Value 'Period ends before last day of daterange
m = Application.Match(CLng(CDate(EndDate.Value)), DateRange, 0)
Set MatchED = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(m).Column)
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, MatchED.Column))
Case Is > LastDate.Value 'Period ends after last day of daterange
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
Case Is = LastDate.Value 'period ends on last day of daterange
Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
End Select
End Select
If PeriodRange Is Nothing Then
CountPeriod = 0
Else:
If IsMissing(CountWhat3) = True Then
If IsMissing(CountWhat2) = True Then
CountPeriod = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1)
Else:
CountPeriod = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1) _
+ Application.WorksheetFunction.CountIf(PeriodRange, CountWhat2)
End If
Else:
CountPeriod = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1) _
+ Application.WorksheetFunction.CountIf(PeriodRange, CountWhat2) _
+ Application.WorksheetFunction.CountIf(PeriodRange, CountWhat3)
End If
End If
End FunctionAlso, There are approximately 900 cells per sheet with this formula and calculation is running slow. If there are any recommendations on speeding this process, that would be appreciated.
