当没有对象时，VBA for Excel 抛出“对象变量或未设置块变量”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22735093/
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 for Excel throws "Object variable or with block variable not set" when there is no Object
提问by wardzin
In my code, I have declared these variables:
在我的代码中，我声明了这些变量：
Dim Field_Name, Datatype, row As Integer
Then, inside a For loop, I have this code:
然后，在 For 循环中，我有以下代码：
Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
row = Worksheets(i).UsedRange.Find("Field Name").row + 1
However, that code throws the "Object variable or with block variable not set" run-time error. According to the API, the Range.Column and Range.row property is a read-only Long. I have tried making the datatype of my variables to Long, but with no success. It would appear that VBA expecting me to do
但是，该代码会引发“对象变量或块变量未设置”运行时错误。根据 API，Range.Column 和 Range.row 属性是只读的 Long。我曾尝试将我的变量的数据类型设置为 Long，但没有成功。看起来VBA希望我做
Set Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
Set row = Worksheets(i).UsedRange.Find("Field Name").row + 1
However, said variables are not objects, so doing that throws the "Object required" compile error.
但是，所述变量不是对象，因此这样做会引发“需要对象”编译错误。
Any help with this would be greatly appreciated. If you're not sure about how to fix it, then any workarounds or alternative ways to get the column number and row number of a cell would be greatly appreciated.
对此的任何帮助将不胜感激。如果您不确定如何修复它，那么将不胜感激任何获取单元格列号和行号的变通方法或替代方法。
回答by Matroid
Even though this is an old question, I'd like to say something too.
虽然这是个老问题，但我也想说几句。
I had the same problem to get this error while using the .Findmethod. I came to this question and so others will do the same.
我在使用该.Find方法时遇到了同样的问题。我来到这个问题，所以其他人也会这样做。
I found a simple solution to the problem:
我找到了一个简单的问题解决方案：
When Finddoes not find the specified string it returns Nothing. Calling anything directly after Findwill lead to this error. So, your .Columnor .rowwill throw an error.
当Find没有找到指定的字符串时，它返回Nothing。之后直接调用任何内容Find都会导致此错误。所以，你的.Columnor.row会抛出一个错误。
In my case I wanted an Offsetof the found cell and solved it this way:
在我的情况下，我想要一个Offset找到的单元格并通过以下方式解决它：
Set result = Worksheets(i).Range("A:A").Find(string)
If result Is Nothing Then
'some code here
ElseIf IsEmpty(result.Offset(0, 2)) Then
'some code here
Else
'some code here
End If
回答by John Joseph
Simplified answer:
简化答案：
Your .Find call is throwing the error.
您的 .Find 调用正在引发错误。
Simply adding "Set " to that line will address the problem. i.e...
只需在该行添加“Set”即可解决问题。IE..
Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
Without "Set," you are attempting to assign "nothing" to a variable. "Nothing" can only be assigned to an object.
如果没有“Set”，您将尝试为变量分配“nothing”。“Nothing”只能分配给一个对象。
You can stop reading here unless you would like to understand what all the other (valid, worthwhile) fuss was about your code.
你可以停止阅读这里，除非你想了解所有其他（有效的，值得的）关于你的代码的大惊小怪。
To paraphrase all of the (warranted) code critiquing, your Dim statement is bad. The first two variables are not being "typed" and end up as variants. Ironically, this is why the solution I just described works.
解释所有（有保证的）代码批评，你的 Dim 语句是糟糕的。前两个变量没有被“输入”并最终成为变体。具有讽刺意味的是，这就是我刚刚描述的解决方案有效的原因。
If you do decide to clean up that Dim statement, declare DataType as a variant...
如果您确实决定清理该 Dim 语句，请将 DataType 声明为变体...
Dim DataType as variant
回答by CaptainABC
What about the below code:
下面的代码怎么样：
For i = 1 to 1 ' change to the number of sheets in the workbook
Set oLookin1 = Worksheets(i).UsedRange
sLookFor1 = "Field Name"
Set oFound1 = oLookin1.Find(What:=sLookFor1, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not oFound1 Is Nothing Then
Field_Name = oFound1.Column
RRow = oFound1.Row +1
' code goes here
Else
Msgbox "Field Name was not found in Sheet #" & i
End If
Set oLookin2 = Worksheets(i).UsedRange
sLookFor2 = "Datatype"
Set oFound2 = oLookin2.Find(What:=sLookFor2, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not oFound2 Is Nothing Then
DataType = oFound2.Column
' code goes here
Else
Msgbox "Datatype was not found in Sheet #" & i
End If
Next i
回答by Chasester
This is an old old post - but I ran across it when I was having trouble figuring out why I suddenly could not import a PDF export into my excel sheet.
这是一篇旧帖子 - 但是当我无法弄清楚为什么我突然无法将 PDF 导出导入到我的 Excel 表中时，我偶然发现了它。
For me the problem was a row I was trying to match on was merged - did a simple unmerge for the entire sheet first and it worked like a charm.
对我来说，问题是我试图匹配的一行被合并了 - 首先对整个工作表进行了简单的取消合并，它就像一个魅力。
'////// Select and open file
FieldFileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*") 'pick the file
Set frBook = Workbooks.Open(FieldFileName, UpdateLinks:=0, ReadOnly:=True, AddToMru:=False)
For Each mySheet In frBook.Worksheets
mySheet.Cells.UnMerge
Next mySheet
