Searching question

[Robert Crandal]

My sheet contains several rows of data and the number or
rows will grow each day. At the end of the year, I'm
guessing that this sheet will contain between 500 and 600
rows of data.

I created a search macro which basically steps though each
row one at a time (using a For-Next loop). I then do simple
string comparison to check if the row of data matches the
search criteria.

If anyone else here was given the task of creating a similar
searching system in Excel, what would you do?? I often
hear about using auto filters and VLOOKUP & MATCH
functions, so I often wonder if my search method is
too simple minded or not.

I'd appreciate any opinions. thank u

 

[Bob Umlas]

Sub SearchSheet()
Dim Srch As String
Srch = InputBox("What are you searching for?")
If Srch = "" Then Exit Sub
On Error Resume Next
Set rg = Cells.Find(Srch, Lookat:=xlPart)
If Not rg Is Nothing Then
rg.Select
Else
MsgBox Srch & " not found."
End If
End Sub

 

[Robert Crandal]

Will this code only find the first instance and then stop?
I'm just curious, since I've never used that .Find()
function before.

 

[Dave Peterson]

This code selects the first match it finds and stops.

But if you look in VBA's help for FindNext, you'll see a way to loop through to
find more/all.