Excel VBA range.find 行动起来
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21403916/
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 range.find acting up
提问by Tyler Durden
Im trying to use range.find to look up a value within a column, and return the matching value from the next column.
我尝试使用 range.find 查找列中的值，并从下一列返回匹配的值。
I recorded the find() using the macro recorder, which seemed to work fine for a while, but for some reason it's now giving me an error. As far as I can tell I haven't changed anything that should affect this bit of code.
我使用宏记录器记录了 find() ，它似乎可以正常工作一段时间，但由于某种原因，它现在给了我一个错误。据我所知，我没有改变任何应该影响这段代码的东西。
This is what I have
这就是我所拥有的
Public Function look_up_id(id, table)
Worksheets(table).Activate
Cells.Find(What:=id, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
look_up_id = ActiveCell.Offset(0, 1).Value
End Function
The error I'm now getting is:
我现在得到的错误是：
Object variable or With block variable not set
对象变量或未设置块变量
Any idea why this is now happening?
知道为什么会这样吗？
All the resources I can find on range.find() look like I'm doing it right...
我可以在 range.find() 上找到的所有资源看起来都做对了...
Cheers - David
干杯 - 大卫
回答by Dmitry Pavliv
Try to use this code instead (when Finddoesn't find anything, it returns Nothingand then you tried to do sth like this Nothing.Activate, and this triggers an error):
尝试改用此代码（当Find没有找到任何内容时，它返回Nothing，然后您尝试这样做Nothing.Activate，这会触发错误）：
Dim res As Range
Set res = Worksheets(table).Cells.Find(What:=id, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not res Is Nothing Then
look_up_id = res.Offset(0, 1).Value
End If
回答by Siddharth Rout
Try this
尝试这个
Public Function look_up_id(id, table) As Variant
Dim ws As Worksheet
Dim aCell As Range
look_up_id = "Not Found"
Set ws = ThisWorkbook.Sheets(table)
With ws
Set aCell = .Cells.Find(What:=id, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not aCell Is Nothing Then _
look_up_id = aCell.Offset(, 1).Value
End With
End Function
More on .FindHERE
更多关于.Find这里
