MrExcel Publishing
Your One Stop for Excel Tips & Solutions

search workbook

Posted by Michael on August 04, 2000 12:43 PM

Is is possible to do a search of all the sheets in a workbook vs going to each sheet and doing a search? i.e., seaching for the name "Mike" in the workbook.

Best regards,


Posted by Ivan Moala on August 04, 0100 4:22 PM

Hi Micheal
Here is a routine
by Jan Karel Pieterse

Give credit to Jan

'This procedure searches through all worksheets in a workbook.

Sub FindItAll()
Dim oSheet As Object
Dim Firstcell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
WhatToFind = Application.InputBox("What are you looking for ?", "Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Firstcell Is Nothing Then
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & Firstcell.Address)
On Error Resume Next
While (Not NextCell Is Nothing) And (Not NextCell.Address = Firstcell.Address)
Set NextCell = Cells.FindNext(After:=ActiveCell)
If Not NextCell.Address = Firstcell.Address Then
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & NextCell.Address)
End If
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
End If
End Sub


Posted by Michael on August 05, 0100 5:33 PM


Hello Ivan,

Thanks for the information ... it works.
Best regards....