iserror函数的模式 - vba excel

Question

我很难找到与vba宏和excel模式的编码。我测试了该表，并且它正在做我想要的东西，直到我删除了用于测试它坠毁时的值。我假设错误是由于模式函数返回一个带空值的错误而引起的。我试图插入iserror行，但是在语法上遇到问题。

如果某些单元格留空，我还需要计算模式。

帮助与模式功能和iserror将不胜感激。还应该使用模式还是mode.single？

在此先感谢

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

试试这个

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