iserror function for mode - vba excel

Question

I am having difficulty with the coding for finding the mode with a vba macro and excel. I tested the sheet and it was doing what I wanted until I removed the values I had used for testing when it crashed. I am assuming that the error was caused as the mode function is returning an error with blank values. I have tried to insert an iserror line but am having issues with the syntax.

I also need to have the mode calculated if some of cells are left blank.

Help with the mode function and the iserror would be greatly appreciated. Also should I be using mode or mode.single?

Thanks in advance

    Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect
Application.EnableEvents = False
'Application.ScreenUpdating = False

Application.DisplayStatusBar = False

'Application.Calculation = xlCalculationManual


Dim finalrow As Long, finalname As Long, currentname As Long, obs() As Variant, col As Integer, stage As Variant, DoB As Date, GLD As String, GLDmin As Single





'find final row
finalrow = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).row

    If finalrow > 3 Then

        ActiveSheet.Range(Cells(finalrow - 1, 1), Cells(finalrow - 1, 26)).Locked = True
        ActiveSheet.Range(Cells(finalrow - 1, 1), Cells(finalrow - 1, 3)).Interior.Color = RGB(200, 200, 200)
        ActiveSheet.Range(Cells(finalrow, 1), Cells(finalrow, 26)).Locked = False
        ActiveSheet.Cells(finalrow + 1, 1).Locked = False

   'lookup name on children sheet for dob and cohort
        DoB = Application.WorksheetFunction.VLookup(ActiveSheet.Cells(finalrow, 1).Value, Sheet1.Range("A:B"), 2, False)
        ActiveSheet.Cells(finalrow, 2).Value = DoB
        ActiveSheet.Cells(finalrow, 3).Value = Application.WorksheetFunction.VLookup(ActiveSheet.Cells(finalrow, 1).Value, Sheet1.Range("A:L"), 12, False)
    'calculate age at time of observation
        If Not ActiveSheet.Cells(finalrow, 4) = "" Then
            If Day(DoB) <= Day(ActiveSheet.Cells(finalrow, 4)) Then
                Cells(finalrow, 5).Value = DateDiff("m", DoB, ActiveSheet.Cells(finalrow, 4))
            Else
                Cells(finalrow, 5).Value = DateDiff("m", DoB, ActiveSheet.Cells(finalrow, 4)) - 1
            End If
        End If

     'read observations to find mode

        'If Not Application.WorksheetFunction.IsError(Application.WorksheetFunction.Mode(Sheet3.Range("I" & finalrow, "Y" & finalrow))) Then

        stage = Application.WorksheetFunction.Mode(Sheet3.Range("I" & finalrow, "Y" & finalrow))
        ActiveSheet.Cells(finalrow, 6).Value = stage
        'End If


        'set cell font colour red if behind stage
        For col = 9 To 25
            If Not ActiveSheet.Cells(finalrow, col) = "" Then
                If ActiveSheet.Cells(finalrow, 5) > 100 * (ActiveSheet.Cells(finalrow, col) - Int(ActiveSheet.Cells(finalrow, col))) Then
                    ActiveSheet.Cells(finalrow, col).Font.Color = RGB(255, 0, 0)
                Else
                    ActiveSheet.Cells(finalrow, col).Font.Color = RGB(0, 0, 0)
                End If
            End If
        Next

        'set GLD
        GLDmin = Application.WorksheetFunction.Min(Sheet3.Range("I" & finalrow, "T" & finalrow))
        If GLDmin < 30 Then
            GLD = ""
        ElseIf GLDmin < 40 Then
            GLD = "Emerging"
        ElseIf GLDmin < 60 Then
            GLD = "Expected"
        Else
            GLD = "Exceeding"
        End If
        ActiveSheet.Cells(finalrow, 7).Value = GLD


    Else
        ActiveSheet.Cells(finalrow + 1, 1).Locked = False
    End If


Application.EnableEvents = True
'Application.ScreenUpdating = True

Application.DisplayStatusBar = True

'Application.Calculation = xlCalculationAutomatic


ActiveSheet.Protect


End Sub

Answer 1

try this

On Error Resume Next
    Stage = Application.WorksheetFunction.Mode(Sheet3.Range("I" & finalrow, "Y" & finalrow))
    If Err <> 0 Then
        Stage = 0 ' or whatever you want the stage to be if Mode has error
        Err.Clear
    End If
On Error GoTo 0