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



<(E-Mail Removed)>
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


<(E-Mail Removed)> wrote in message
news:1106092a-bc17-48b6-bf2d-(E-Mail Removed)...
> 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

That worked perfectly. Thank you so much!

On May 22, 2:03*pm, "Patrick Molloy" <patrick_mol...@hotmail.com>
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
>
> <kmz...@gmail.com> wrote in message
>
> news:1106092a-bc17-48b6-bf2d-(E-Mail Removed)...
>
>
>
> > 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 -