VBA 运行时错误 91 未设置对象变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17835065/
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 Runtime error 91 Object variable not Set
提问by Artem Korol
I'm trying to select a range with todays date only, but receiving Runtime error 91 on the Cells.find(TodayLast).Activate line. I can't understand what seems to be the problem.
我试图选择一个仅包含今天日期的范围，但在 Cells.find(TodayLast).Activate 行上收到运行时错误 91。我无法理解似乎是什么问题。
Sub Escalation()
Dim rng As Range
Dim rngsend As Range
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String
Dim rngHeader As Range
Dim TodayFirst As Range
Dim TodayLast As Range
Dim LastDate As String
' Finds the area of today's range
LastRow = Sheets("Escal").Range("A65536").End(xlUp).Row
Cells(LastRow, 1).Activate
Set TodayLast = ActiveCell
Cells.find(TodayLast).Activate
Set TodayFirst = ActiveCell
Range(TodayFirst, TodayLast.Offset(0, 6)).Select
'Sorterar breacharna - Sorts the breaches
Selection.Sort Key1:=Range("G1"), Key2:=Range("B1"), Key3:=Range("D1")
'A loop that divides the various comps and enters a GoSub formula that prepares mails
Cells(TodayFirst.Row, 7).Activate
Set CompanyFirst = ActiveCell
Do Until IsEmpty(CompanyFirst)
Cells.find(What:=CompanyFirst, LookIn:=xlValues, SearchDirection:=xlPrevious).Activate
Set CompanyLast = ActiveCell
GoSub PrepareMail
Cells(CompanyLast.Row + 1, 7).Activate
Set CompanyFirst = ActiveCell
Loop
Cells(LastRow, 1).Select
Exit Sub
回答by Jon Crowell
It's possible that "Escal" isn't the activesheet. You fully qualify the range when finding the last row, but don't in any of the subsequent code. You also have a bunch of unnecessary activates and selects, which lead to exactly the kind of bug you are experiencing. Declare object variables and use those instead. You'll lose the overhead of making sure the correct object is active and avoid this kind of bug in the future.
“Escal”可能不是活动表。您在查找最后一行时完全限定范围，但不在任何后续代码中。您还有一堆不必要的激活和选择，这会导致您遇到的错误类型。声明对象变量并使用它们。您将失去确保正确对象处于活动状态的开销，并避免将来出现此类错误。
You should also ALWAYS require variable declaration. (Tool --> Options...)
您还应该始终要求变量声明。（工具 --> 选项...）
Try re-writing your code like this:
尝试像这样重新编写代码：
Sub AvoidRTE91()
Dim ws As Worksheet
Dim lastRow As Long
Dim todayLast As Range
Dim todayFirst As Range
Dim todayRange As Range
Set ws = ThisWorkbook.Sheets("Escal")
lastRow = ws.Range("A65536").End(xlUp).Row
' added message for debugging purposes. Remove once you figure out what is wrong.
MsgBox ws.Cells(lastRow, 1) & " is in cell " & ws.Cells(lastRow, 1).Address
Set todayLast = ws.Cells(lastRow, 1)
Set todayFirst = ws.Cells.Find(todayLast)
' you don't have to select the range to work with it
Set todayRange = ws.Range(todayFirst, todayLast.Offset(0, 6))
' now instead of using selection, use todayRange...
End Sub
回答by Roberto
In this line:
在这一行：
Cells.find(What:=CompanyFirst, LookIn:=xlValues, SearchDirection:=xlPrevious).Activate
You set what, lookinand searchdirection. Then if you execute again the routine, the first call to find in this line:
你设置what，lookin和searchdirection。然后如果你再次执行例程，第一次调用 find 在这一行：
Cells.find(TodayLast).Activate
Fails because is trying to use the what, lookinand searchdirectionyou defined previously.
失败，因为正在尝试使用what,lookin并且searchdirection您之前定义过。
That's why it works fine only the first time you execute.
这就是为什么它只有在您第一次执行时才能正常工作。
(Thanks to your question I solved my problem :))
（感谢您的问题，我解决了我的问题:)）
