InputBox

[Benoit]

Hello,

I have an Excel workbook with many worksheets. I would
like to use a macro that will use the info entered in the
input box to search all the worksheets in the workbook.

Can you help???

Thanks!!!

 

[Don Guillett]

try this from the archives

posted by J.E

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("Found " & strSearchString & _
" at " & foundCell.Address & vbNewLine & _
"(" & counter & " of " & countTot & ")", _
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 'SearchAllSheets

 

[Dave Peterson]

If you're doing this for a general utility, you may want to steal a copy of Jan
Karel Pieterse's FlexFind at:
http://www.bmsltd.co.uk/MVP/Default.htm