Excel VBA 宏：“For Each ... In ...”循环中的“Object required”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26628345/
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 macro: "Object required" in "For Each ... In ..." loop
提问by emberfiend
This is a VBA macro in Excel 2013.
这是 Excel 2013 中的 VBA 宏。
I'm looping through the cells in Sheet 1, Col B. For each cell, I want take its value and search for that in Sheet 2, Col A. If it's found, I want to take the corresponding value in Sheet 2, Col B and place it in the appropriate row in Sheet 1, Col E.
我正在遍历 Sheet 1, Col B 中的单元格。对于每个单元格，我想获取它的值并在 Sheet 2, Col A 中搜索它。如果找到，我想获取 Sheet 2, Col 中的相应值B 并将其放在 Sheet 1, Col E 中的适当行中。
These sheets:
这些表：
Tagging Automatic Categories
B E A B
hat cake Delicious cake.
cake
arm
Should become:
应该变成：
Tagging Automatic Categories
B E A B
hat cake Delicious cake.
cake Delicious cake.
arm
Code:
代码：
Sub AutoCategorize()
Dim c As Range
Dim searchRng As Range
For Each c In Sheets("Tagging").Range("B6:B500").Cells ' loop through cells to do the lookup based on
If Not c.Value Is Nothing Then ' if there is something in the cell
If c.Offset(0, 3).Value Is Nothing Then ' make sure the cell to fill is empty
With Sheets("Automatic Categories").Range("A2:A500") ' using the cells we're looking up in...
Set searchRng = .Find(What:=c.Value) ' find it
If Not searchRng Is Nothing Then ' make sure we've found a thing
If Not searchRng.Offset(0, 1).Value Is Nothing Then ' make sure it has a corresponding entry
Set c.Offset(0, 3).Value = searchRng.Offset(0, 1).Value ' fill it in
End If
End If
End With
End If
End If
Next
End Sub
My problem, I think, is with my understanding of how Excel-VBA structures the data. The MSDN is really unhelpful in this regard, unfortunately, and I've only managed to piece together a lot of how things work from experimentation.
我认为，我的问题在于我对 Excel-VBA 如何构造数据的理解。不幸的是，MSDN 在这方面真的没有帮助，而且我只是设法从实验中拼凑出很多事情是如何工作的。
When I run the code, I get
当我运行代码时，我得到
Run-time error '424': Object required
and the debugger highlights
和调试器突出显示
If Not c.Value Is Nothing Then
Can anyone shed some light on what's causing the error? I'm pretty sure my logic is okay, but as I say I'm not 100% on how to reference cells/how the data structures work.
任何人都可以阐明导致错误的原因吗？我很确定我的逻辑没问题，但正如我所说，我不是 100% 了解如何引用单元格/数据结构如何工作。
I'm new to VB and Excel macros so shout if there's a better way to structure things. This is also my first StackOverflow post so please let me know if I've done anything wrong.
我是 VB 和 Excel 宏的新手，所以如果有更好的方法来组织事物，请大喊大叫。这也是我的第一篇 StackOverflow 帖子，所以如果我做错了什么，请告诉我。
采纳答案by citizenkong
The error here is that If Not c.Value Is Nothingis checking if the value contained in cell c is an object, and that that object has not been instantiated.
这里的错误If Not c.Value Is Nothing是检查单元格 c 中包含的值是否为object，并且该对象尚未实例化。
Since a cell value is a primitive type (really a variant), then the correct check to use is either
由于单元格值是原始类型（实际上是变体），因此要使用的正确检查是
If c.Value <> vbNullString
If c.Value <> vbNullString
or
或者
If IsEmpty(c)
If IsEmpty(c)
your later use of Is Nothing, in If Not searchRng Is Nothingis correct, as this is checking if a Range object contains Nothing.
您稍后使用Is Nothing, inIf Not searchRng Is Nothing是正确的，因为这是检查 Range 对象是否包含Nothing.
回答by Barry
c.valuerefers to the value in the cell (text, number, date). This will never be an Object. One way to check the value of a cell (even with only spaces) is
c.value指单元格中的值（文本、数字、日期）。这永远不会是一个对象。检查单元格值的一种方法（即使只有空格）是
If Length(Trim(c.Value)) > 0 Then ...
回答by Fratyx
The Valueof a cell doesn't return an object but a Variant value. Only objects can be tested with Nothing. Just write
该Value小区的不返回一个对象，而是一个Variant值。只能使用Nothing. 写就好了
If c.Value <> ""
If c.Value <> ""
