Edit : Find....not working between sheets

M

medicchic

Hello,

I have created a workbook that shows supplies for 20 differen
cabinets. Each cabinet is listed on a worksheet. When I want to fin
a particular item, I select all the sheets and go to Edit, Find an
type in the item and it takes me to the correct cabinet. The problem
am currently running into, is that if there are the same item i
multiple cabinets, it just goes to the last one, instead of starting a
the first one and continuing forth. So....If I have gloves of differen
types in cabinet 1, 4, and 7 and perform this function, it should tak
me to 1 and then when I click on Next, it should go forward to 4, etc.


I am using Excel '97.

Any help would be greatly appreciated.
Carolyn :eek
 
J

Jim May

I like this code in a standard module;
Paste it into a new std module and try it.
It might work for you, or else modify it.
Jim May

Sub SearchAllSheets()
Dim strSearchString As String
Dim ws As Worksheet
Dim foundCell As Range
Dim returnValue As Variant
Dim loopAddr As String
Dim countTot As Long
Dim counter As Long

strSearchString = InputBox(Prompt:= _
"Enter a title or other value to search for.", _
Title:="Search Workbook")

For Each ws In Worksheets
countTot = countTot + Application.CountIf( _
ws.UsedRange, "=" & strSearchString)
Next ws
If countTot = 0 Then
MsgBox strSearchString & " not found."
Else
counter = 0
For Each ws In Worksheets
With ws
.Activate
Set foundCell = .Cells.Find( _
What:=strSearchString, _
LookIn:=xlValues, _
LookAt:=xlPart)
If Not foundCell Is Nothing Then
loopAddr = foundCell.Address
Do
counter = counter + 1
foundCell.Activate
returnValue = MsgBox("In " & ActiveSheet.Name & " Found " &
strSearchString & _
" at " & foundCell.Address & vbNewLine & _
"(" & counter & " in " & countTot + 1 & ")", _
vbOKCancel)
If returnValue = vbCancel Then Exit For
Set foundCell = .Cells.FindNext( _
After:=foundCell)
Loop While Not foundCell Is Nothing And _
foundCell.Address <> loopAddr
End If
End With
Next ws
End If
End Sub
 
Top