SEARCH BOX

  • Thread starter Thread starter JOHN
  • Start date Start date
J

JOHN

First thank you in advance for any help. I want to put a search box on the
first sheet of my program to enter data, either a name or an assigned number,
and have it search for and go to that line of information on another sheet.
Can I do this or do you have another suggestion?
 
This should do it. Change sheet name and column numbers to suit

Sub nameornumberfind()
With Sheets("sheet34")
mv = InputBox("Enter name or number")
If IsNumeric(mv) Then
'MsgBox "number"
mc = 4
Else
'MsgBox "text"
mc = 1
End If
Set mf = .Columns(mc).find(What:=mv, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not mf Is Nothing Then
'MsgBox "found at " & mf.Row
Application.Goto .Cells(mf.Row, mc)
End If
End With
End Sub
 
If preferred, you could assign the macro to a worksheet_change event using a
cell to make it automatic
 
Back
Top