.FindNext 在 .Find 函数后失败 (excel vba)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10986326/
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
.FindNext failing after a .Find function (excel vba)
提问by Evan Patrick McCann
I am trying to use .Findand .FindNextto search through a single column of data. I first need to find the first cell containing the value "Total". The cell I'm trying to get to is the third cell AFTER the "Total" cell to contain the value "Tech". It is known for certain that the Cells(1, 1) does not contain "Tech" or "Total".
我正在尝试使用.Find和.FindNext搜索单列数据。我首先需要找到包含值“总计”的第一个单元格。我要访问的单元格是“总计”单元格之后包含值“技术”的第三个单元格。可以肯定的是，Cells(1, 1) 不包含“Tech”或“Total”。
Dim FirstTotal As Range
Dim SearchRng As Range
Dim ResultRng As Range
Set SearchRng = Range("A:A")
Set FirstTotal = SearchRng.Find(What:="Total", After:=Cells(1, 1), SearchDirection:=xlNext)
Set ResultRng = SearchRng.Find(What:="Tech", After:=FirstTotal, SearchDirection:=xlNext)
SearchRng.FindNext().Activate
SearchRng.FindNext().Activate
About 50% of the times I've run this code, I've been stopped by a type mismatch error on the line beginning with Set ResultRng =. The rest of the time, the code has run all the way through, but the results look as though the final two lines of code were ignored completely.
在我运行此代码的大约 50% 的时间里，我被以Set ResultRng =. 其余时间，代码一直运行，但结果看起来好像完全忽略了最后两行代码。
I suspect that the answer here is pretty elementary, but I'm pretty new to excel vba and no resources I've found so far have answered this. Please help!
我怀疑这里的答案很基本，但我对 excel vba 还很陌生，到目前为止我找不到任何资源来回答这个问题。请帮忙！
回答by Doug Glancy
If "Total" isn't found, then FirstTotal will be Nothing, which will result in a Type Mismatch when you try to use FirstTotal for the "After" argument in the ResultRange Find (the 2nd line). This will prevent that error:
如果未找到“Total”，则 FirstTotal 将为 Nothing，当您尝试将 FirstTotal 用于 ResultRange Find（第 2 行）中的“After”参数时，这将导致类型不匹配。这将防止该错误：
Set FirstTotal = SearchRng.Find(What:="Total", After:=Cells(1, 1), SearchDirection:=xlNext)
If Not FirstTotal is Nothing Then
Set ResultRng = SearchRng.Find(What:="Tech", After:=FirstTotal, SearchDirection:=xlNext)
End If
Generally speaking any dependent Finds need to be treated this way.
一般来说，任何依赖的 Finds 都需要这样处理。
Clearly, some kind of Else statement is required here, but I don't know what that would be.
显然，这里需要某种 Else 语句，但我不知道那会是什么。
回答by Siddharth Rout
Would this help?
这会有帮助吗？
Topic: .Find and .FindNext In Excel VBA
主题：Excel VBA 中的.Find 和.FindNext
Link: http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/
链接：http: //www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/
Extract From the link:
摘自链接：
Sub Sample()
Dim oRange As Range, aCell As Range, bCell As Range
Dim ws As Worksheet
Dim ExitLoop As Boolean
Dim SearchString As String, FoundAt As String
On Error GoTo Err
Set ws = Worksheets("Sheet3")
Set oRange = ws.Columns(1)
SearchString = "2"
Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
FoundAt = aCell.Address
Do While ExitLoop = False
Set aCell = oRange.FindNext(After:=aCell)
If Not aCell Is Nothing Then
If aCell.Address = bCell.Address Then Exit Do
FoundAt = FoundAt & ", " & aCell.Address
Else
ExitLoop = True
End If
Loop
Else
MsgBox SearchString & " not Found"
End If
MsgBox "The Search String has been found at these locations: " & FoundAt
Exit Sub
Err:
MsgBox Err.Description
End Sub
