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