To chaju: Help with searching all sheets for value.

[Tom Urtis]

Chaju:

Regarding your post at
http://www.mrexcel.com/board/viewtopic.php?topic=7742&forum=2&1 --

I'm responding to you this way because your subject line only contains the word "Help!", which does not give the board's readers an indication of what the actual topic is.

Just a friendly suggestion, it's a good practice to provide a consise description in the subject line, maybe such as (in your case) "How to search all sheets for value?". This will improve your chances for a reply, and save other board browsers some time in reading (or skipping over) your topic.

As for a possible solution, if I understand your question correctly, try a macro like this:

Sub SearchAllSheets()
Dim sVal
Dim counter As Integer, sheetCount As Integer
Dim bSh, bCell
Application.ScreenUpdating = False
On Error Resume Next
bCell = ActiveCell.Address
bSh = ActiveSheet.Name
sVal = InputBox("Please enter the value you want to locate:", "What are you looking for?")
If sVal = "" Then
MsgBox "Please click OK to return from whence you came.", 64, "Search cancelled."
Exit Sub
Else
If IsError(CDbl(sVal)) = False Then sVal = CDbl(sVal)
sheetCount = ActiveWorkbook.Sheets.Count
counter = 1
Do Until counter > sheetCount
Sheets(counter).Activate
Cells.Find(What:=sVal, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
If ActiveCell.Value = sVal Then Exit Do
counter = counter + 1
Loop
If ActiveCell.Value <> sVal Then
MsgBox "The value you entered could not be found." & vbCrLf & _
"Please click OK to get back into the workbook.", 64, "Sorry..."
Application.Goto Reference:=Worksheets(bSh).Range(bCell)
Application.ScreenUpdating = True
Exit Sub
End If
End If
Application.ScreenUpdating = True
End Sub

Hope this helps.