选择“查找”的第二结果与VBA [英] Selecting the second result of a "Find" with VBA
问题描述
我试图使这样做,以便我可以找到第二个结果为灯,以防万一出现这个术语。下面的代码发现在考虑范围内的第一次出现。
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
推荐答案
FindNext
提供你想要的东西。使用它很简单:执行第一次搜索,就像您现在正在做的那样(尽管通过将结果分配给 Range
),并将结果范围作为起始点 FindNext中
。在这里,您有一个适合您特定要求的示例代码( secondAddress
是第二次出现Light的地址
,如果有的话):
FindNext
delivers 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 (secondAddress
is the Address
of the second occurrence of "Light", if any):
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
这篇关于选择“查找”的第二结果与VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!