Use excel as a searcher

  • Thread starter Thread starter ims
  • Start date Start date
I

ims

Dear all,

I have a database, I want to use excel as a searcher.
By inputting a key word excel will search the whole database and display all
the records containing this key word.
What's the best and simplest way to do this?
Is there any sample program on the net for my easy reference.
Thanks.

ims
 
Dear Tom,

What's the meaning of "Database isn't definitive"?
I find the path you given me can't help me a lot.

regards,
ims
 
You could try the following code in a vba module:

First name 2 ranges
"Database" as the cells in you DB
"KeyWord" as a single cell ABOVE your db or on another sheet.


Option Explicit

Sub SearchDB()
Dim oCell As Range
Dim r As Range
Dim Found As Boolean
Dim KWord As String
Dim firstAddress As String

KWord = Range("KeyWord")

For Each r In Range("Database").Rows
Found = False
With r
Set oCell = .Find(KWord, LookIn:=xlValues)
If Not oCell Is Nothing Then
firstAddress = oCell.Address
Do
Found = True
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <
firstAddress
End If
If Not Found Then r.EntireRow.Hidden = True

End With
Next


End Sub


TO SHOW YOUR DATABASE again, RightClick all rows in the DB>>Unhide
 
Saying I have a database really says nothing very useful.

Where is the database. Gocush has assumed it is in an excel spreadsheet. I
have assumed it is external to excel - but even assuming that, where is it -
access, sql server, oracle, something else?

Maybe I should have said the term database is not self defining.
 
Back
Top