VBA Search Box - needs try again option

[LORDMARKS]

Hi All

I found some code online to run a basic value search on a column. I have modified it to suit my needs, but as I would like to add the function to "Try Again" if the returned result is not the correct one.

I need this because the search is for names, and the sheet used by lots of people, so they might search for type "James" and get the wrong result, so a button or function to say try again, that would look for the next occurance would be great.

Thanks if anyone can help with this

Sub SearchName()
Application.ScreenUpdating = False
  Dim Prompt As String
  Dim RetValue As String
  Dim Found As String
  Dim Rng As Range
  Dim RowCrnt As Long
  
Unlocker
Range("C:BC").Interior.ColorIndex = 0
Range("F10").Select

  Prompt = ""
  With Sheets("RECORD")
    Do While True
      RetValue = InputBox(Prompt & "Who are you looking for?")
      If RetValue = "" Then
        Exit Do
      End If
      Set Rng = .Columns("E:E").Find(What:=RetValue, After:=.Range("E1"), _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
      If Rng Is Nothing Then
        Prompt = "I could not find """ & RetValue & """"
      Else
        RowCrnt = Rng.Row
        Found = Range("E" & RowCrnt).Value
        Application.ScreenUpdating = True
        Range("F" & RowCrnt).Select
        Range("C" & RowCrnt & ":" & "BC" & RowCrnt).Interior.ColorIndex = 22
        Prompt = "I have highlighted """ & Found & """ on row " & RowCrnt
      End If
      Prompt = Prompt & vbLf
    Loop
  End With
Locker
End Sub

 

[NeonRedSharpie]

Sub SearchName()Application.ScreenUpdating = False
  Dim Prompt As String
  Dim RetValue As String
  Dim Found As String
  Dim Rng As Range
  Dim RowCrnt As Long
  
Unlocker
Range("C:BC").Interior.ColorIndex = 0
Range("F10").Select
tryagain:
  Prompt = ""
  With Sheets("RECORD")
    Do While True
      RetValue = InputBox(Prompt & "Who are you looking for?")
      If RetValue = "" Then
        Exit Do
      End If
      Set Rng = .Columns("E:E").Find(What:=RetValue, After:=.Range("E1"), _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
      If Rng Is Nothing Then
        Prompt = "I could not find """ & RetValue & """"
        GoTo tryagain
      Else
        RowCrnt = Rng.Row
        Found = Range("E" & RowCrnt).Value
        Application.ScreenUpdating = True
        Range("F" & RowCrnt).Select
        Range("C" & RowCrnt & ":" & "BC" & RowCrnt).Interior.ColorIndex = 22
        Prompt = "I have highlighted """ & Found & """ on row " & RowCrnt
      End If
      Prompt = Prompt & vbLf
    Loop
  End With
Locker
End Sub

I'm not sure where you want to try again, but I put it in front of "Prompt = """

 

[LORDMARKS]

thanks Neon

Reading back I wasnt very clear. At the moment the code asks for part of name via input box and searches the column for fisrt occurance, on "OK" it then highlights and selcets the row, else exits. It allows you to search more than one name and highlights each one in turn.

The problem I have is if there are several people with the same part name eg: ROBERT and ROBERTSON. The code will highlight the first one or exit, but I would like the option to select try again or next etc.. so it would continue search for the next occurance.


Hope thats clearer,,, thanks for the help

 

[LORDMARKS]

With a bit of playing and using Neons loop idea i got it to work great. If the retuned value is NG you just keep clicking NO until the correct result is displayed.

Sub SearchNameNext()
Application.ScreenUpdating = False
  Dim Prompt As String
  Dim RetValue As String
  Dim Found As String
  Dim Rng As Range
  Dim RowCrnt As Long
  Dim Correct As String
  Dim StartRow As String
  
Unlocker
Range("C:BC").Interior.ColorIndex = 0
Range("F10").Select
StartRow = 1
  Prompt = ""
  With Sheets("RECORD")
    Do While True
      RetValue = InputBox(Prompt & "Who are you looking for?")
      If RetValue = "" Then
        Exit Do
      End If
      
TryAgain:
      Set Rng = .Columns("E:E").Find(What:=RetValue, After:=.Range("E" & StartRow), _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
      If Rng Is Nothing Then
        Prompt = "I could not find """ & RetValue & """"
      Else
        RowCrnt = Rng.Row
        Found = Range("E" & RowCrnt).Value
        Application.ScreenUpdating = True
        Range("F" & RowCrnt).Select
        
        Output = MsgBox("Are you looking for """ & Found & "", vbYesNoCancel)
        
        If Output = 6 Then
Range("C" & RowCrnt & ":" & "BC" & RowCrnt).Interior.ColorIndex = 22
ElseIf Output = 7 Then
StartRow = RowCrnt
GoTo TryAgain
Else
Locker
Exit Sub
End If

      End If
      Prompt = Prompt & vbLf
    Loop
  End With
Locker
End Sub

I have tried to use the code in another file, where I need to search the row instead, but it converts the column letter to a number so it wont work? I not great at working with column numbers, so if anyone know how to convert that would be great.

Thanks again