☰

运行时错误 91- Excel VBA

tag-icon • tag-icon microsoft-excel vba runtime-error
运行时错误 91- Excel VBA

我是 VBA 新手，并且遇到了以下代码问题，每次选择任何单元格（G 列中的单元格除外）时都会出现以下错误；

“运行时错误 91 - 未设置对象变量或块变量”

Private Sub Worksheet_Change(ByVal Target As Range)
    Static mailSent As Boolean

    If Not mailSent And Range("G10:G250").Find("YES", MatchCase:=False).Count() > 0 Then
        SendMail
        mailSent = True
    End If
End Sub

Private Sub SendMail()
    With CreateObject("Outlook.Application").createitem(0)
         .To = "helpdesk171@***.com"
            .Subject = "*** Facility Manager Update"
            .Body = "Hi Property Services, " & vbNewLine & vbNewLine & "Update made by Facility Manager which requires your attention." & vbNewLine & vbNewLine & "Click Here <\\Internal_Gold Facility Inspection Action Tracker.xlsx>" & vbNewLine & vbNewLine & "Please amend the drop down in Column G accordingly (received/complete)" & vbNewLine & vbNewLine & " Kind regards "
            .Send
     End With
End Sub

当我单击调试时，它会突出显示以下行 - “如果没有 mailSent 并且 Range（“G10：G250”）。Find（“YES”，MatchCase：=False）。Count（）> 0 则”

有什么想法需要改变什么来避免出现此错误？

提前致谢 ：）

答案1

我不确定你的布尔值，但你需要进行 Is Nothing 测试，以防 Find 没有匹配，例如

Private Sub Worksheet_Change(ByVal Target As Range)
    Static mailSent As Boolean
    Dim found As Range
    Set found = Range("G10:G250").Find("YES", MatchCase:=False)
    If found Is Nothing Then Exit Sub
    If Not mailSent And found.Count > 0 Then
        SendMail
        mailSent = True
    End If
End Sub

相关内容