Code to find a string but need to handle multiple matches

[mecerrato]

I have this code provided from a very helpful coder it searches for a string within a range, it currently searches 2 columns and then takes you to the first match.

Was wondering if it can be tweaked so that if there is more than 1 match to ask the user if he wants to go to the next match?

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "F3" Then
      Set Fnd = Range("C11:D" & Rows.Count).Find(Target.Value, , , xlPart, , , False, , False)
      If Not Fnd Is Nothing Then
         Range("C" & Fnd.Row).Activate
         Range("C" & Fnd.Row).Copy
         'Fnd.Activate
         Application.EnableEvents = False
         Range("F3").Value = Null
         Application.EnableEvents = True
      Else
         MsgBox "Not Found"
         Application.EnableEvents = False
         Range("F3").Value = Null
         Application.EnableEvents = True
         
   End If
   End If
End Sub

 

[Peter_SSs]

if there is more than 1 match to ask the user if he wants to go to the next match?

Before you go to the next match (assuming there is one) what are you going to do with the first one that is copied with this code but not pasted anywhere?

 

[mecerrato]

Before you go to the next match (assuming there is one) what are you going to do with the first one that is copied with this code but not pasted anywhere?

I would put the copy command after the user picked the right match.

 

[Peter_SSs]

OK, see how this goes.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Fnd As Range
  Dim FirstAddr As String
  Dim Resp As VbMsgBoxResult
   
  If Target.Address(0, 0) = "F3" Then
    Set Fnd = Range("C11:D" & Rows.Count).Find(What:=Target.Value, LookAt:=xlPart, MatchCase:=False)
    If Not Fnd Is Nothing Then
      FirstAddr = Fnd.Address
      Do
        Range("C" & Fnd.Row).Activate
        Resp = MsgBox(Prompt:="Is this the one you want?", Buttons:=vbYesNoCancel)
          Select Case Resp
            Case vbYes
              Range("C" & Fnd.Row).Copy
            Case vbNo
              Set Fnd = Range("C11:D" & Rows.Count).FindNext(After:=Fnd)
            Case vbCancel
              MsgBox "OK, Cancelled"
          End Select
      Loop Until Resp = vbYes Or Resp = vbCancel Or Fnd.Address = FirstAddr
    Else
      MsgBox "Not Found"
    End If
    Application.EnableEvents = False
    Range("F3").ClearContents
    Application.EnableEvents = True
  End If
End Sub

 

[mecerrato]

OK, see how this goes.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Fnd As Range
  Dim FirstAddr As String
  Dim Resp As VbMsgBoxResult
  
  If Target.Address(0, 0) = "F3" Then
    Set Fnd = Range("C11:D" & Rows.Count).Find(What:=Target.Value, LookAt:=xlPart, MatchCase:=False)
    If Not Fnd Is Nothing Then
      FirstAddr = Fnd.Address
      Do
        Range("C" & Fnd.Row).Activate
        Resp = MsgBox(Prompt:="Is this the one you want?", Buttons:=vbYesNoCancel)
          Select Case Resp
            Case vbYes
              Range("C" & Fnd.Row).Copy
            Case vbNo
              Set Fnd = Range("C11:D" & Rows.Count).FindNext(After:=Fnd)
            Case vbCancel
              MsgBox "OK, Cancelled"
          End Select
      Loop Until Resp = vbYes Or Resp = vbCancel Or Fnd.Address = FirstAddr
    Else
      MsgBox "Not Found"
    End If
    Application.EnableEvents = False
    Range("F3").ClearContents
    Application.EnableEvents = True
  End If
End Sub

Thank you, that worked great, I just made one modification, Range("F3").ClearContents changed to Range("F3").Value=Null because F3 is a merged cell. I appreciate you writing this code for me

 

[Peter_SSs]

Thank you, that worked great ... I appreciate you writing this code for me

You're welcome.

I just made one modification, Range("F3").ClearContents changed to Range("F3").Value=Null because F3 is a merged cell.

That's fine. You could have used this too.

Range("F3").MergeArea.ClearContents