VBA Excel 错误对象变量或未设置块变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17429950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel error object variable or with block variable not set
提问by mgrobins
The code below will find adjacent word but I tried to add an error handler if a word is not in the spreadsheet. I'm getting error object variable or with block variable not set. What is the problem? or can you help fix the error message so that if the word is not found the msgbox displays MsgBox "Sorry the text was not found please try again. Macro stopping". Thanks!
下面的代码将查找相邻的单词，但如果电子表格中没有单词，我尝试添加错误处理程序。我收到错误对象变量或未设置块变量。问题是什么？或者您能否帮助修复错误消息，以便如果未找到该词，则 msgbox 会显示 MsgBox“对不起，未找到文本，请重试。宏停止”。谢谢！
Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String
Dim Number As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "report" 'word not in spreadsheet
Set match = ws.Cells.Find(findMe)
findOffset = match.Offset(, 1).Value 'error occurs object variable or with block variable not set on this line
Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & findMe & """,A1:AZ96,1)))))")
If (Not match Is Nothing) Then
MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """. It is found "" " & Number & """ times!"
Else
'not match
MsgBox "Sorry the text was not found please try again. Macro stopping"
End If
End Sub
采纳答案by Julien Marrec
As JosieP said, This code works as intended (I just tried it)
正如 JosieP 所说，这段代码按预期工作（我刚试过）
Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String
Dim Number As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "report" 'word not in spreadsheet
Set match = ws.Cells.Find(findMe)
If (Not match Is Nothing) Then
findOffset = match.Offset(, 1).Value 'error occurs object variable or with block variable not set on this line
Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & findMe & """,A1:AZ96,1)))))")
MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """. It is found "" " & Number & """ times!"
Else
'not match
MsgBox "Sorry the text was not found please try again. Macro stopping"
End If
End Sub
