Searching over multiple worksheets
Posted by Richard Shore on September 25, 2001 11:57 PM
Is it possible to search for data over multiple worksheets? I have 4 sheets all with hundreds of entries and rather than do an Edit / Find, on each worksheet I'd rather do a global search that will look through all worksheets.
Thanx
Rich
Posted by Barrie Davidson on September 26, 2001 6:57 AM
Rich, you can use this code to search through your sheets. It will stop on the first occurrence it finds.
Sub Search_All()
' Written by Barrie Davidson
Dim searchValue
Dim counter As Integer, sheetCount As Integer
Dim startSheet, startCell
Application.ScreenUpdating = False
On Error Resume Next
startCell = ActiveCell.Address
startSheet = ActiveSheet.Name
searchValue = InputBox("Find What", "FIND:")
If searchValue = "" Then Exit Sub
If IsError(CDbl(searchValue)) = False Then searchValue = CDbl(searchValue)
sheetCount = ActiveWorkbook.Sheets.Count
counter = 1
Do Until counter > sheetCount
Sheets(counter).Activate
Cells.Find(What:=searchValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
If ActiveCell.Value = searchValue Then Exit Do
counter = counter + 1
Loop
If ActiveCell.Value <> searchValue Then
MsgBox ("Value entered not found")
Application.Goto Reference:=Worksheets(startSheet).Range(startCell)
Application.ScreenUpdating = True
Exit Sub
End If
Application.ScreenUpdating = True
End Sub
Regards,
BarrieBarrie Davidson
Posted by Richard Shore on September 26, 2001 7:15 AM
Barrie,
You're a star, cheers
Rich
