VBA 错误：对象变量或未设置变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11917841/
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 error: Object variable or with variable not set
提问by AME
I receiving a strange error when running this subroutine in VBA:
在 VBA 中运行此子例程时收到一个奇怪的错误：
Sub NameColumns()
' name key columns for later reference in formulas
Dim startdatecol As Integer
' name start date column
startdatecol = ActiveSheet.Cells.Find(What:="Start Date", after:=[a1], LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
End Sub
Run time error '91': Object variable or With variable not set
运行时错误“91”：对象变量或未设置变量
Any ideas on how I can fix this subroutine error? And why it is occurring?
关于如何修复此子例程错误的任何想法？为什么会发生？
Thanks, AME
谢谢，阿梅
回答by mkingston
The problem is that Findis not finding the cell.
问题是Find没有找到单元格。
You will find (pun intended) that the following is true:
你会发现（双关语）以下是正确的：
MsgBox ActiveSheet.Cells.Find(What:="Start Date", after:=[a1], LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False) Is Nothing
The first thing you should do is fix your search so that it finds the cell you're looking for.
您应该做的第一件事是修复您的搜索，以便它找到您正在寻找的单元格。
Edit:
编辑：
Maybe a change that would better illustrate the problem is this:
也许可以更好地说明问题的更改是这样的：
Dim found as Range
Dim startDateCol as Integer
Set found = ActiveSheet.Cells.Find(What:="Start Date", after:=[a1], LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not found Is Nothing Then startDateCol = found.Column
MsgBox startDateCol 'This will be zero if the "Start Date" cell wasn't found.
Edit to respond to comment:
编辑以回复评论：
'This should find the exact text "Start Date" (case sensitive) in the header row.
'A cell containing, for example "The Start Date" will not be matched.
Set found = ActiveSheet.Range("1:1").Find("Start Date", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True)
