Macro for Finding account numbers

J

JL

I am a beginner at writing macros so I am asking for assistance. What I
would like to do is have a macro that pulls from a cell or input box an
account number the user is wanting to see and locates on that same worksheet.
Basically identical to the Find function but with a macro for the input.
Thanks in advance for any assistance.
 
C

Chip Pearson

Try something like the following:

Dim FindWhat As String
Dim FoundCell As Range
FindWhat = InputBox("Find What", "Find")
If StrPtr(FindWhat) = 0 Then
Exit Sub
End If
On Error Resume Next
Set FoundCell = Range("A1:A100").Find(what:=FindWhat, _
lookat:=xlWhole, LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found at: " & FoundCell.Address
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
T

tcowen

Another option besides a macro is to have named ranges. Read up on
those and see if it would solve your problem. Then all they have to do
is use the F5 key for "go to" and pick the name range they want. There
are ways to build named ranges from lists so it is somewhat simple.
Another method is to have a list and create hyperlinks. You can also
create drop down lists. Macros can be as easy or as complicated as you
need, but some end users get "freaked" when they see the "Enable macros"
choice when a file opens. A simple macro would be to designate an input
cell (they would enter the account in this cell) and then your macro
would do a find in a designated area and go-to that cell. Sounds like
you need to have a list first though. Turn on the macro recorder and
start with something simple. If it works, expand it to your specific
situation. That's how I taught myself.

TcO
 
Top