Selecting Second Find Results Using VBA

Question

I am trying to make it possible for me to find a second result for "lights", in the case of various occurrences for this term. In the code below, the first occurrence in the range in question was detected.

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 

Answer 1

FindNext provides what you want. It’s easy to use: do the first search as you are doing right now (though by assigning the result to Range ) and take the resulting range as a starting point for FindNext . Here you have an example of code adapted to your specific requirements ( secondAddress is the Address second appearance 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 

Answer 2

I found an even easier way, as it seems like I had a similar problem.

If you have 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 below:

 Cells.Find(What:="xxxx", After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select 

All this finds the first occurrence of "xxxx", then the second code finds "xxxx", but starts the search from the result of the first search code (which was ActiveCell).