使用 VBA 选择“查找”的第二个结果

我正在努力做到这一点，以便我可以找到“lights”的第二个结果，以防该术语出现各种情况。下面的代码查找所考虑范围内的第一个匹配项。

    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 is the 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