Search Engine in an Excel SpreadSheet

  • Thread starter Thread starter Gary A
  • Start date Start date
G

Gary A

Is there any way to include a search engine in a spreadsheet? I would like
the user to enter a word into a cell and maybe click on a button to search
the workbook much in the way a web search works?
Thanks in advance
Gary
 
Try this. Right click sheet tab>view code>copy/paste this>SAVE>type search
text into cell a1 of that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
For Each ws In Worksheets
With ws.Cells
Set c = .Find(Target, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.Address <> "$A$1" Then
MsgBox ws.Name & c.Address
Exit Sub
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Next ws
End Sub
 
Don,

That works, but only finds the first instance and does not find text
within a cell, e.g. does not find July in "the month of July".

How do you find the next instance and can it take you through them
like a standard search?
 
Hi
you may change the line
Set c = .Find(Target, LookAt:=xlWhole)
to
Set c = .Find(Target, LookAt:=xlPart)
 
It will find more if you dont
exit sub ' (comment out)
and if you want part change from xlwhole to xlpart
 
Gentlemen,
Thanks for the tip. I will try it here either today or tomorrow. I
appreciate the help!!
Gary
 
Back
Top