search box

[ajborj]

Hi to everyone. I have a little project and I hope someone could assist me
for this. I have a worksheet with four columns but the number of data exceeds
4000 rows. I understand excel can search using the function "ctrl+f" but what
I would like to achieve is that, I need to create a search box that would
return the matched keywords and highlight all data or matches in the
worksheet.
The worksheet would appear like this:
State City Institution Name Number

I tried creating userform that would search for the three fields state, city
and institution name and return all available information. Its like searching
data from the database. Can someone assist me on this?

 

[Michael]

You should try using autofilter,
Set row 1 on your worksheet as you described:

State City Institution Name Number

Select all four columns and then from the Main Menu bar select:
Data-> Filter -> Autofilter
Then you can set your criteria based on any of the above columns or a
combination of them.

 

[john]

As already suggested, Autofilter would probably do most of what you want but
if you want to use a macro then see if following helps:

Sub FindValue()
Dim Myws As Worksheet
Dim Search As Variant
Dim MyTitle As String
Dim MyPrompt As String

MyTitle = "Search"

MyPrompt = "Search By:" & Chr(10) & _
"State, City, Institution, Name or Number"

Set Myws = Worksheets("Sheet1") '<< change as required

startsearch:
Search = Application.InputBox(prompt:=MyPrompt, Title:=MyTitle, Type:=2)

If VarType(Search) = vbBoolean Then

If Search = False Then
Debug.Print "cancelled"
Exit Sub
End If

ElseIf Search <> "" Then

With Myws

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("A1:E" & lastrow)

.Interior.ColorIndex = xlNone

Set c = .Find(Search, LookIn:=xlValues)

If Not c Is Nothing Then

firstAddress = c.Address

Do
.Range(Cells(c.Row, 1), Cells(c.Row,
5)).Interior.ColorIndex = 36

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress

Else

msg = MsgBox("Search Value Not Found", vbInformation,
MyTitle)

End If

End With

End With

Else

GoTo startsearch

End If
End Sub

 

[ajborj]

Thanks John.

I've already tried the code and it worked perfectly well. I really
appreciate your help. Just one question, is it possible that the serach for
the keyword would be specific for the different columns? What I would like to
achieve is to have a form to appear like this:
Enter: State
Enter: City
Enter: Institution Name
Search
For example, if I enter the state, it would return the city, institution
name and numbers. And if I input the keywords on the three fields it would
return the specific values that matches the search. Is that possible? I know
it's some kind of complicated. I'm just really having difficulty in coming up
with the code since I'm just starting to use vb.