UDF gives #Value Error in Spreadsheet

[ebraatz]

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:

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 Function

The function is entered into the cell as:

=CountPeriod1(FirstDayPK, LastDayP1,AugSeptDates,E7:AH7, "A")

TIA!!

 

[JoeMo]

Are FirstDayPK, LastDayP1 and AugSeptDates all named ranges?

 

[ebraatz]

Are FirstDayPK, LastDayP1 and AugSeptDates all named ranges?

Sorry for not clarifying. Yes, those are all named ranges within the worksheet.

 

[Roxxien]

You should put option explicit at the top of your module and calling your function with the following code.
You should then start that sub, if an error appear, you will see where it is.

Sub test()
msgbox [COLOR=#333333]CountPeriod1(FirstDayPK, LastDayP1,AugSeptDates,E7:AH7, "A")
[/COLOR]End sub

Edit : Forget the end sub statement ...

 

[Norie]

Have you tried stepping through the code with F8?

You can do that by setting a breakpoint here,

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

and then entering the example formula you posted.

Now when you step through it's unlikely you'll get any error messages but it should help you to determine where in the function things are failing.

 

[ebraatz]

Have you tried stepping through the code with F8?

You can do that by setting a breakpoint here,

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

and then entering the example formula you posted.

Now when you step through it's unlikely you'll get any error messages but it should help you to determine where in the function things are failing.

Could you explain how to do that? I have never been able to figure out how to set that up.

And for the record, I have run the test sub suggested, and it gives the desired result. I'm happy to share my file if that helps anyone.

 

[Roxxien]

you are saying that when you run the test sub I gives the desired result but not when you use that same function in your Excel sheet?

 

[Norie]

What are FirstDayPK, LastDayP1 and AugSeptDates here?

=CountPeriod1(FirstDayPK, LastDayP1,AugSeptDates,E7:AH7, "A")

 

[shg]

References like this

Set LastDate = [COLOR="#FF0000"]Cells[/COLOR](DateRange.Row, m)

... refer to the active worksheet, not the sheet containing the ranges of interest.

 

[shg]

You would need something like ...

Set LastDate = DateRange.Worksheet.Cells(DateRange.Row, m)