Adding Options When User Cancels/Cannot Find

  • Thread starter Thread starter kmzito
  • Start date Start date
K

kmzito

Hi everyone,

Much thanks in advance for any help.

I'm putting together the two below macros. The first to ask the user
for a vendor name, and then it searches (easy enough!). When I search
for a vendor that is not there, I get an error to debug. I'd like an
error box to pop up. I just learned userforms today so I'm thinking
maybe that would work as an option? Any advice would be greatly
appreciated.

(1)*********

Dim sUsername As String
Dim sPrompt As String
sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)

Sheets("list").Select
Range("C2").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select
 
Find returns a Range object.
If whatever is not found then Nothing is returned.
So...

Dim fCell as range
'Remove".Select" from the end.
Set fCell = Find(What: ...fill in)
If fCell is Nothing Then
MsgBox "Not Found"
Else
'something
End If
--
Jim Cone
Portland, Oregon USA



<[email protected]>
wrote in message
Hi everyone,
Much thanks in advance for any help.
I'm putting together the two below macros. The first to ask the user
for a vendor name, and then it searches (easy enough!). When I search
for a vendor that is not there, I get an error to debug. I'd like an
error box to pop up. I just learned userforms today so I'm thinking
maybe that would work as an option? Any advice would be greatly
appreciated.

(1)*********

Dim sUsername As String
Dim sPrompt As String
sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)
Sheets("list").Select
Range("C2").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
 
This wouldn't work. Any other ideas? I keep getting an error message.
Note I made an addition to the macro.

SORT_DATE
SORT_VENDOR

Dim sUsername As String
Dim sPrompt As String
sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)

Sheets("list").Select
Range("C2").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select

If sUsername = "" Then
MsgBox "You have cancelled the search."
Sheets("welcome").Select
Range("a1").Select

Exit Sub
End If
End Sub
 
Sub username()

Dim sUsername As String
Dim sPrompt As String
Dim found As Range
sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, "Select Name...", "")

Sheets("list").Activate
Set found = Cells.Find(What:=sUsername, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not found Is Nothing Then
found.Select
Else
MsgBox "Unable to find " & sUsername

End If

End Sub
 
That worked perfectly. Thank you so much!

Sub username()

  Dim sUsername As String
    Dim sPrompt As String
    Dim found As Range
    sPrompt = "Please enter vendor name"
    sUsername = InputBox(sPrompt, "Select Name...", "")

    Sheets("list").Activate
    Set found = Cells.Find(What:=sUsername, After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not found Is Nothing Then
        found.Select
    Else
        MsgBox "Unable to find " & sUsername

    End If

End Sub











- Show quoted text -
Click to expand...
 
Back
Top