vba 根据单元格值查找行的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42723407/
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
Function to find row based on cell value
提问by Ace16150s
I have the following code to find the row where a certain value resides, however it keeps debugging with
我有以下代码来查找某个值所在的行，但是它会继续调试
Error 91 "Object variable or With block variable not set"
错误 91“未设置对象变量或块变量”
Which is weird because I use the same structure to find a row before this procedure and it works.
这很奇怪，因为我使用相同的结构在此过程之前查找行并且它有效。
wbs.Activate
Cells.Find(What:="Name", After:=wbs.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
NameRow = ActiveCell.Row
回答by CallumDA
Your only problem is that when you don't have "Name" on your worksheet .Findreturns Nothingrather than a Rangeobject. You then get an error because you are trying to use .Activateon Nothing.
您唯一的问题是，当您的工作表上没有“名称”时，将.Find返回Nothing而不是Range对象。然后你会得到一个错误，因为你试图使用.Activateon Nothing。
The solution
解决方案
There is no need to use Activateand ActiveCell, just define your variables well and use them! Here's a working example:
不需要使用Activateand ActiveCell，只需定义好变量并使用它们即可！这是一个工作示例：
Sub test()
Dim wks As Worksheet
Dim r As Range
Dim rowNumber As Long
Set wks = ThisWorkbook.Worksheets("sheet1") 'update for your worksheet name
'.Find returns a Range object or Nothing
Set r = wks.Cells.Find(What:="Name", LookAt:=xlWhole)
If Not r Is Nothing Then
rowNumber = r.Row
End If
MsgBox rowNumber
End Sub
回答by Ace16150s
Apparently there was a space after "Name", so I should have been looking for "Name ". Revised the search and it worked the way I had it but thank you @CallumDA for your clean answer. So my problem is that it was not able to find my lookup variable which in turn meant it could not activate the found cell resulting in the Error 91! Much appreciated for your quick reply.
显然“Name”后面有一个空格，所以我应该一直在寻找“Name”。修改了搜索并按照我的方式工作，但感谢@CallumDA 的清晰回答。所以我的问题是它无法找到我的查找变量，这反过来意味着它无法激活找到的单元格，导致错误 91！非常感谢您的快速回复。
