Hi all,
I have a simple sub that lets users input a name in a input box and it will
find the name in a list(and move one cell right for data input.
One problem: fatal error 91 happens when a name is entered that does not
match the list where I'd prefer a msgbox or just returning to the input
box.
Second problem is the name lookup. Using the Find method if user enters
"ones" it will find "ones", and "jones" and "phones" where I would want to
find just the names that begin with the letters typed into the input box.
I've included the code and any help is always appreciated.

Dim pname As String
'user enters name or partial name a sub finds it in a list
pname =inputbox("Enter name")
' this if handles cancel and no entry to inputbox
If pname = "" Then Exit Sub
Range("a3:a147").Select
Selection.find(What:=pname).Activate
ActiveCell.Offset(0, 1).Range("A1").Select



--
jeffP

One way:

Dim vResult As Variant
Dim rFound As Range
Dim sFoundAddress As String
Dim pName As String
Dim bValid As Boolean

Do
vResult = Application.InputBox("Enter name", Type:=2)
If vResult = False Then Exit Sub 'User cancelled
Loop Until vResult < ""
With Range("A3:A147")
Set rFound = .Cells.Find( _
What:=vResult, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=True)
If Not rFound Is Nothing Then
sFoundAddress = rFound.Address
Do
With rFound
If (.Text = vResult) Or (.Text Like vResult & " *") Or _
(.Text Like "* " & vResult) Or _
(.Text Like "* " & vResult & " *") Then bValid = True
End With
If Not bValid Then Set rFound = _
.Cells.FindNext(after:=rFound)
Loop Until bValid Or rFound.Address = sFoundAddress
End If
If bValid Then
rFound.Offset(0, 1).Select
Else
MsgBox vResult & " not found"
End If
End With


In article ,
"jeffP" wrote:

Hi all,
I have a simple sub that lets users input a name in a input box and it will
find the name in a list(and move one cell right for data input.
One problem: fatal error 91 happens when a name is entered that does not
match the list where I'd prefer a msgbox or just returning to the input
box.
Second problem is the name lookup. Using the Find method if user enters
"ones" it will find "ones", and "jones" and "phones" where I would want to
find just the names that begin with the letters typed into the input box.
I've included the code and any help is always appreciated.

Dim pname As String
'user enters name or partial name a sub finds it in a list
pname =inputbox("Enter name")
' this if handles cancel and no entry to inputbox
If pname = "" Then Exit Sub
Range("a3:a147").Select
Selection.find(What:=pname).Activate
ActiveCell.Offset(0, 1).Range("A1").Select