VBA Search Box - needs try again option

LORDMARKS

New Member
Joined
Jun 5, 2014
Messages
39
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

Code:
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
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
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

New Member
Joined
Jun 5, 2014
Messages
39
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

New Member
Joined
Jun 5, 2014
Messages
39
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.

Code:
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
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,972
Messages
5,767,394
Members
425,410
Latest member
SmittyT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top