Excel vba 对象变量或块变量未设置错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27991222/
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
Excel vba Object variable or With block variable not set error
提问by ajs
I am coding in excel vba and get error 91 or a Object variable or With block variable not set error when i run my code and I am unsure as to why. I define and set my variables so I don't know what could cause the error. The relevant code is below
我在 excel vba 中编码并在运行我的代码时收到错误 91 或对象变量或块变量未设置错误，我不确定为什么。我定义并设置了我的变量，所以我不知道是什么导致了错误。相关代码如下
Sub Button5_Click()
Dim i As Integer
Dim Month As Range
Dim Avg As Range
Dim Target As Range
Dim Incorrect As Range
Set Month = Range("J19")
Set Avg = Range("H19")
Set Incorrect = Range("A19")
Set Target = Range("M19")
'Range("A" & Rows.Count).End(xlUp).Row
For i = 0 To 1000
If IsEmpty(Avg) Then
If Month.Find("jan") <> "" Then
Set Target = Range("M19")
The error is on the If Month.Find("jan") <> "" then part of the code.
错误出现在 If Month.Find("jan") <> "" then 部分代码上。
The full code is here:
完整代码在这里：
Sub Button5_Click()
Dim i As Integer
Dim Month As Range
Dim Avg As Range
Dim Target As Range
Dim Incorrect As Range
Set Month = Range("J19")
Set Avg = Range("H19")
Set Incorrect = Range("A19")
Set Target = Range("M19")
'Range("A" & Rows.Count).End(xlUp).Row
For i = 0 To 1000
If IsEmpty(Avg) Then
If Month.Find("jan") <> "" Then
Set Target = Range("M19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("feb") <> "" Then
Set Target = Range("O19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("mar") <> "" Then
Set Target = Range("Q19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("apr") <> "" Then
Set Target = Range("S19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("may") <> "" Then
Set Target = Range("U19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("jun") <> "" Then
Set Target = Range("W19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("jul") <> "" Then
Set Target = Range("Y19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("aug") <> "" Then
Set Target = Range("AA19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("sep") <> "" Then
Set Target = Range("AC19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("oct") <> "" Then
Set Target = Range("AE19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("nov") <> "" Then
Set Target = Range("AG19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
Else
Set Target = Range("AI19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
End If
Else
If Month.Find("jan") <> "" Then
Set Target = Range("N19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("feb") <> "" Then
Set Target = Range("P19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("mar") <> "" Then
Set Target = Range("R19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("apr") <> "" Then
Set Target = Range("T19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("may") <> "" Then
Set Target = Range("V19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("jun") <> "" Then
Set Target = Range("X19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("jul") <> "" Then
Set Target = Range("Z19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("aug") <> "" Then
Set Target = Range("AB19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("sep") <> "" Then
Set Target = Range("AD19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("oct") <> "" Then
Set Target = Range("AF19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
ElseIf Month.Find("nov") <> "" Then
Set Target = Range("AH19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
Else
Set Target = Range("AJ19")
If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
End If
End If
Set Month = Month.Offset(1, 0)
Set Incorrect = Incorrect.Offset(1, 0)
Set Avg = Avg.Offset(1, 0)
Set Target = Target.Offset(1, 0)
Next i
End Sub
Any help is greatly appreciated.
任何帮助是极大的赞赏。
回答by Sean Kuhlman
Range.Findreturns a Rangeso you need to evaluate it as an object:
Range.Find返回 aRange所以你需要把它作为一个对象来评估：
If Not Month.Find("jan") Is Nothing Then
Set Target = Range("M19")
End If
If you need to work with the returned Rangeyou can use something like this:
如果您需要处理返回的内容，Range您可以使用以下内容：
Dim foundCell As Range
Set foundCell = Month.Find("jan")
If Not foundCell Is Nothing Then
Set Target = Range("M19")
End If
The reference for Range.Findis here.
的参考Range.Find是here。
