Adding Options When User Cancels/Cannot Find
 

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

Adding Options When User Cancels/Cannot Find
 

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




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

Adding Options When User Cancels/Cannot Find
 

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

Adding Options When User Cancels/Cannot Find
 

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


wrote in message
...
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

Adding Options When User Cancels/Cannot Find
 

That worked perfectly. Thank you so much!

On May 22, 2:03*pm, "Patrick Molloy"
wrote:
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

wrote in message

...



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- Hide quoted text -

- Show quoted text -