Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OPENFILENAME A P I. User cancels ? | Excel Programming | |||
Deleting a menu in BeforeClose but user Cancels the Save | Excel Programming | |||
Problem is adding options to ComboBox | Excel Programming | |||
How to present User with Print Options | Excel Programming | |||
How to extend user options | Excel Programming |