Hi,

I have a search term cell where the value the user wants to search can be
typed. I want to then click a search button which will search the entire
workbook for this term. I am happy to use the dialogue but am unsure of how
to go about it. I have tried recording a macro but this didnt work.

Thanks

In xl2002+, you could just show the edit|Find dialog and tell the user to click
on the options button and select entire workbook.

Or you may want to try Jan Karel Pieterse's FlexFind:
http://www.oaltd.co.uk/MVP/

It'll work for all versions.

rjw24 wrote:

Hi,

I have a search term cell where the value the user wants to search can be
typed. I want to then click a search button which will search the entire
workbook for this term. I am happy to use the dialogue but am unsure of how
to go about it. I have tried recording a macro but this didnt work.

Thanks

--

Dave Peterson

In the macro below, this line

myFindString = InputBox("Enter the key word for finding", _
, "What to find")

asks for the search term. You can change it to something like this to pick up a cell value:

myFindString = Worksheets("SheetName").Range("A2").Value

You may want to change lookAt:=xlPart to lookAt:=xlWhole

HTH,
Bernie
MS Excel MVP

Sub FindAllValuesInWorkbook()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFindString As String
Dim firstAddress As String
Dim mySht As Worksheet

myFindString = InputBox("Enter the key word for finding", _
, "What to find")
For Each mySht In ActiveWorkbook.Worksheets
With mySht.Cells

Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "On sheet " & mySht.Name & ", " & myFindString & " was not Found"
GoTo NotFound:
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
'Then do what you want with all
'the cells that have been found, like
MsgBox "On sheet " & mySht.Name & ", " & myFindString & " was found in " & d.Address
NotFound:
Next mySht

End Sub


"rjw24" wrote in message
...
Hi,

I have a search term cell where the value the user wants to search can be
typed. I want to then click a search button which will search the entire
workbook for this term. I am happy to use the dialogue but am unsure of how
to go about it. I have tried recording a macro but this didnt work.

Thanks

Try this to look in each sheet

Sub findinworksheets()
what = InputBox("Enter search word")
'or
'what=Range("a1")

For Each ws In Worksheets
Set myfind = ws.UsedRange.Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not myfind Is Nothing Then Application.Goto myfind, True
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rjw24" wrote in message
...
Hi,

I have a search term cell where the value the user wants to search can be
typed. I want to then click a search button which will search the entire
workbook for this term. I am happy to use the dialogue but am unsure of
how
to go about it. I have tried recording a macro but this didnt work.

Thanks

Thanks for coming back to me

Unfortunately, I cant download the first responses template because I'm at
work which prohibits downloading from unconfirmed sources.

I ran your code and it worked. However, I wonder if theres a way of having
the find dialogue box results which shows a list of all those matches and is
clickable using this method.





"Don Guillett" wrote:

Try this to look in each sheet

Sub findinworksheets()
what = InputBox("Enter search word")
'or
'what=Range("a1")

For Each ws In Worksheets
Set myfind = ws.UsedRange.Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not myfind Is Nothing Then Application.Goto myfind, True
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rjw24" wrote in message
...
Hi,

I have a search term cell where the value the user wants to search can be
typed. I want to then click a search button which will search the entire
workbook for this term. I am happy to use the dialogue but am unsure of
how
to go about it. I have tried recording a macro but this didnt work.

Thanks

I don't understand the question.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rjw24" wrote in message
...
Thanks for coming back to me

Unfortunately, I cant download the first responses template because I'm at
work which prohibits downloading from unconfirmed sources.

I ran your code and it worked. However, I wonder if theres a way of having
the find dialogue box results which shows a list of all those matches and
is
clickable using this method.





"Don Guillett" wrote:

Try this to look in each sheet

Sub findinworksheets()
what = InputBox("Enter search word")
'or
'what=Range("a1")

For Each ws In Worksheets
Set myfind = ws.UsedRange.Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not myfind Is Nothing Then Application.Goto myfind, True
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rjw24" wrote in message
...
Hi,

I have a search term cell where the value the user wants to search can
be
typed. I want to then click a search button which will search the
entire
workbook for this term. I am happy to use the dialogue but am unsure of
how
to go about it. I have tried recording a macro but this didnt work.

Thanks