Newbee Q: How to make search field in excel sheet?

[kandinsky]

Hi.
I am using Excel in a rather stupid way. I work in sales and have al
my company leads in here, so I can keep them in check. What I woul
like to have though, is a field where I can type part of a compan
name, and then the Excel ark goes to the first cell that matches...

It seems so simple, but I have almost never used Excel before, so I'
lost...

Any quick tips out there

 

[Tom Ogilvy]

Use Edit=>Find

 

[kandinsky]

Yup... That's actually the functions i'm lookng for, but for reasons
don't really understand myself, I would like to implement the funktio
into a textfield+submit button...

Any ideas?

(Except the obvious "Use another more apropiate program for tha
stuff"...

 

[Dave Peterson]

Record a macro when you do Edit|find.

But then you'll need to modify it slightly to stop errors in typing.

This recorded macro:
Columns("A:A").Select
Selection.Find(What:="asdf", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

could become:

Option Explicit
Sub SearchForName()

Dim FoundCell As Range
Dim CellWithName As Range


With Worksheets("sheet1")
Set CellWithName = .Range("A1")
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName.Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub