Personal Message instead of Run time error

[Mountaineer00]

I have a workbook that runs a macro to look up a value based on city entered. When a city is entered that is spelled wrong or does not exist, I receive a run time error and the auto filter is still used and I have to go to filter and deselect. What I would like to do is if the city is not found in list, have a personal message stating something like "City not Found" then the macro cease and reset back to the original box of "Please Enter City"<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
John Davis helped tremendously with the lookup function and I'm trying to expand on that. Below is the code that I'm currently using for lookup functions. I don't know how to keep from getting the error and not having the filter applied if wrong.<o></o>
<o></o>
Sub Store_Lookup()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
Range("D5").Value = InputBox("Please Enter City Name:", "Lookup By City")<o></o>
Columns("B:B").AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Range("D5").Value
Range("A61:A" & lr).SpecialCells(xlCellTypeVisible).Copy Range("E5")
Columns("B:B").AutoFilter
End Sub<o></o>

 

[AlphaFrog]

Sub Store_Lookup()

    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
    
Retry:
    Range("D5").Value = InputBox("Please Enter City Name:", "Lookup By City")
    If IsEmpty(Range("D5")) Then Exit Sub ' User canceled
    
    If Not Columns("B:B").Find(Range("D5"), , , xlWhole, , , False) Is Nothing Then
        Application.ScreenUpdating = False
        Columns("B:B").AutoFilter Field:=1, Criteria1:=Range("D5").Value
        Range("A61:A" & lr).SpecialCells(xlCellTypeVisible).Copy Range("E5")
        Columns("B:B").AutoFilter
        Application.ScreenUpdating = True
        
    Else
        If MsgBox("City not found." & vbCr & vbCr & "Try again?", _
            vbInformation + vbYesNo, "No Match") = vbYes Then GoTo Retry
    End If
    
End Sub

 

[Ruddles]

Try this:-

[FONT=Courier New][COLOR=black]Sub Store_Lookup()[/COLOR][/FONT]
 
[COLOR=black][FONT=Courier New]Dim lr As Long[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Dim prompt As String[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Dim okcity As Long[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]lr = Cells(Rows.Count, 1).End(xlUp).Row + 1[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]okcity = 0[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]prompt = "Please Enter City Name:"[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Do Until okcity > 0[/FONT][/COLOR]
[COLOR=black][FONT=Courier New] Range("D5").Value = InputBox(prompt, "Lookup By City")  [/FONT][/COLOR]
[COLOR=black][FONT=Courier New] On Error Resume Next[/FONT][/COLOR]
[FONT=Courier New][COLOR=black] okcity = WorksheetFunction.Match(Range("D5").Value, Columns("B"), False)[/COLOR][/FONT]
[FONT=Courier New][COLOR=black] On Error GoTo 0[/COLOR][/FONT]
[COLOR=black][FONT=Courier New][FONT=Courier New] prompt = "Invalid! Please Enter Valid City Name:"[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Loop[/FONT][/COLOR]
 
[FONT=Courier New][COLOR=black]Columns("B:B").AutoFilter[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]Selection.AutoFilter Field:=1, Criteria1:=Range("D5").Value[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]Range("A61:A" & lr).SpecialCells(xlCellTypeVisible).Copy Range("E5")[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]Columns("B:B").AutoFilter[/COLOR][/FONT]
 
[COLOR=black][FONT=Courier New]End Sub<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT] [/COLOR]

This assumes you want to hold the users in the loop until they pick a valid city. If they want to cancel out of the InputBox you'd have to do something like this:-

[FONT=Courier New]Sub Store_Lookup()
 
Dim lr As Long
Dim prompt As String
Dim okcity As Long
 
 lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
 
 okcity = 0
 prompt = "Please Enter City Name:"
 Do Until okcity > 0
   Range("D5").Value = InputBox(prompt, "Lookup By City")
   If IsEmpty(Range("D5")) Then Exit Do
   On Error Resume Next
   okcity = WorksheetFunction.Match(Range("D5").Value, Columns("B"), False)
   On Error GoTo 0
   prompt = "Invalid! Please Enter Valid City Name:"
 Loop
 
 If Not IsEmpty(Range("D5")) Then
   Columns("B:B").AutoFilter
   Selection.AutoFilter Field:=1, Criteria1:=Range("D5").Value
   Range("A61:A" & lr).SpecialCells(xlCellTypeVisible).Copy Range("E5")
   Columns("B:B").AutoFilter
 End If
 
End Sub[/FONT]

 

[Ruddles]

Try this:-
This assumes you want to hold the users in the loop until they pick a valid city. If they want to cancel out of the InputBox you'd have to do something like this...[/QUOTE]

To clarify: the first piece of code assumes you want to hold the users in the loop until they pick a valid city. If you're happy for them to cancel out of the InputBox you'd have to do something like the second piece of code.

 

[Mountaineer00]

Thanks so much for both of your help. I truly appreciate it. Awesome!