I'm making a workbook to track student attendance for the year. There is an identical tab for each month of the year which has a linear calendar that will change depending on the year chosen. At the end of the month I tally the number of Absences, etc for a given term. The terms begin and end in the middle of a month, so i wrote a UDF, Function CountPeriod1, to determine the range to use for CountIf based on the given date range and the start and end dates of the period. The function works perfectly when run with a test sub, but when I use it in the worksheet I get a #Value error. I've read many forums about this problem but none of those situations help me. The Function code is:
The function is entered into the cell as:
=CountPeriod1(FirstDayPK, LastDayP1,AugSeptDates,E7:AH7, "A")
TIA!!
Code:
Function CountPeriod1(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 = Cells(DateRange.Row, DateRange.Columns(1).Column) 'First day of selected DateRange
Set LastDate = DateRange.SpecialCells(xlCellTypeLastCell) 'Last day of selected DateRange
If IsEmpty(LastDate) = True Then
m = DateRange.Find(What:="*", LookIn:=xlValues, searchorder:=xlByColumns, searchdirection:=xlByColumns).Column
Set LastDate = Cells(DateRange.Row, m)
End If
'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 = CountRange
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 = 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 = CountRange
End Select
Case Is < FirstDate.Value 'Period Ends before selected daterange begins
CountPeriod1 = 0
Case Is = FirstDate.Value 'Period ends on first day of Daterange
Set PeriodRange = 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
CountPeriod1 = 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 = 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 = Cells(DateRange.Row, DateRange.Columns(m).Column)
Set PeriodRange = Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, MatchED.Column))
Case Is > LastDate.Value 'period ends after DateRange Ends
Set PeriodRange = 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 = 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 = 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 = Cells(DateRange.Row, DateRange.Columns(m).Column)
Set PeriodRange = Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, MatchED.Column))
Case Is > LastDate.Value 'Period ends after last day of daterange
Set PeriodRange = CountRange
Case Is = LastDate.Value 'period ends on last day of daterange
Set PeriodRange = CountRange
End Select
End Select
If IsMissing(CountWhat3) = True Then
If IsMissing(CountWhat2) = True Then
CountPeriod1 = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1)
Else:
CountPeriod1 = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1) _
+ Application.WorksheetFunction.CountIf(PeriodRange, CountWhat2)
End If
Else:
CountPeriod1 = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1) _
+ Application.WorksheetFunction.CountIf(PeriodRange, CountWhat2) _
+ Application.WorksheetFunction.CountIf(PeriodRange, CountWhat3)
End If
End FunctionThe function is entered into the cell as:
=CountPeriod1(FirstDayPK, LastDayP1,AugSeptDates,E7:AH7, "A")
TIA!!
Last edited:
