使用 VBA 选择“查找”的第二个结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18402800/
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
Selecting the second result of a "Find" with VBA
提问by Richard Pullman
I am trying to make it so that I can find the second result for "lights", in case of having various occurrences for this term. The code below finds the first occurrence in the range under consideration.
我试图做到这一点，以便我可以找到“灯光”的第二个结果，以防该术语出现各种情况。下面的代码在所考虑的范围内查找第一次出现。
Dim ws As Worksheet
Dim rng1 As Range
Dim y As Range
Columns("B:B").Select
Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
Set x = Range(Selection, Selection.End(xlDown)).Offset(0, 3)
Range(x.Address(0, 0)).Select
Selection.Find(What:="Lights", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate
Selection.FindNext(After:=ActiveCell).Select
回答by varocarbas
FindNextdelivers what you want. Using it is easy: perform the first search as you are doing it right now (although by assigning the result to a Range) and take the resulting range as starting point for FindNext. Here you have a sample code adapted to your specific requirements (secondAddressis the Addressof the second occurrence of "Light", if any):
FindNext提供您想要的东西。使用它很容易：按照您现在的方式执行第一次搜索（尽管将结果分配给 a Range）并将结果范围作为 的起点FindNext。在这里，您有一个适合您的特定要求的示例代码（secondAddress是Address第二次出现“Light”的代码，如果有的话）：
Dim foundRange As Range
Dim rangeToSearch As Range
Set rangeToSearch = Selection
Set foundRange = rangeToSearch.Find(What:="Lights", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False) 'First Occurrence
Dim secondAddress As String
If (Not foundRange Is Nothing) Then
foundRange.Activate
Dim count As Integer: count = 0
Dim targetOccurrence As Integer: targetOccurrence = 2
Dim found As Boolean
Do While Not found
Set foundRange = rangeToSearch.FindNext(foundRange)
If Not foundRange Is Nothing Then
count = count + 1
If (count >= targetOccurrence - 1) Then
secondAddress = foundRange.Address
Exit Do
End If
Else
Exit Do
End If
Loop
End If
回答by beginner but learning
I found an even easier way as it sounds like I had a similar problem.
我找到了一种更简单的方法，因为听起来我遇到了类似的问题。
If you simplified your search function:
如果您简化了搜索功能：
Cells.Find(What:="xxxx", After:=Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
Then add another line beneath:
然后在下面添加另一行：
Cells.Find(What:="xxxx", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
All this does is find the first occurrence of "xxxx", then the second code finds "xxxx", but begins searching from the result of the first find code (which was the ActiveCell).
所有这一切都是找到第一次出现的“xxxx”，然后第二个代码找到“xxxx”，但从第一个查找代码（即 ActiveCell）的结果开始搜索。
