Selecting FOUND result.

[pedie]

Hi, how can i make vba to select 2nd FOUND result...
suppose from range.find("x"... i have 4 matching result found. How can i select the 2nd result. Is there a trick to do this?

 

[pgc01]

Hi

Check the help on the Range method .FindNext().

 

[pedie]

Could you please make this work?
How do I make it stop when it the 2nd address/value of search result is selected.

And conditions to handle errors if there is no result and there is only one result.


Thanks again.

Sub FindSecondfromFound()
With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

 

[pgc01]

If you just want the second result you don't need to loop. Just use .Find() to find the first one and then .FindNext() to find the second one.

Ex., finding the string "OK":

Sub FindSecondfromFound()
Dim c As Range
Dim firstAddress As String
 
With Worksheets(1).Range("a1:a500")
    Set c = .Find("OK", LookIn:=xlValues)
    If c Is Nothing Then
        MsgBox "Not found the first time"
    Else
        firstAddress = c.Address
        Set c = .FindNext(c)
        If c.Address = firstAddress Then
            MsgBox "Not found the second time"
        Else
            MsgBox "Address: " & c.Address
        End If
    End If
End With
End Sub

 

[pedie]

Great. Perfect. Thanks alot!