PDA

View Full Version : [SOLVED] my firefiter



lior03
06-21-2005, 11:15 PM
hello
thanks to firefiter i got a macro to search all sheets in a workbook.
how can i make the inputbox appear only once to accept a value to search.
it popup on every sheet.what about if the value i look for appear more
then once in a sheet.

Sub searchers()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim x As String
For Each ws In ActiveWorkbook.Worksheets
Dim rngResult As Range
ws.Cells.Font.ColorIndex = 1
x = InputBox("choose value to search", "hadara")
Set rngResult = ws.Cells.Find(What:=x, LookIn:=xlValue, LookAt:=xlPart)
If rngResult Is Nothing Then
MsgBox x & " not found in " & ws.Name
Else
rngResult.EntireRow.Font.ColorIndex = 3
MsgBox x & " found in " & ws.Name
End If
Application.ScreenUpdating = True
Next ws
End Sub
thanks
moshe

Steiner
06-22-2005, 12:13 AM
It should be sufficient to move the inputbox right before the loop:
Option Explicit


Sub searchers()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim x As String
x = InputBox("choose value to search", "hadara")
For Each ws In ActiveWorkbook.Worksheets
Dim rngResult As Range
ws.Cells.Font.ColorIndex = 1
Set rngResult = ws.Cells.Find(What:=x, LookIn:=xlValue, LookAt:=xlPart)
If rngResult Is Nothing Then
MsgBox x & " not found in " & ws.Name
Else
rngResult.EntireRow.Font.ColorIndex = 3
MsgBox x & " found in " & ws.Name
End If
Application.ScreenUpdating = True
Next ws
End Sub


Daniel

Zack Barresse
06-22-2005, 08:22 AM
So in your search string, if you are looking for "cat", would "The cat" be acceptable as well? Or how about "Cat" or "CAT"? Also, do you want a message box to pop up for every item found or not found on each sheet?

Zack Barresse
06-22-2005, 10:35 AM
Well, maybe this will help ...


Option Explicit

Sub searchers_3()
Dim c As Range, ws As Worksheet, FirstAddress As String
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
On Error GoTo SkipWs
ws.Cells.Font.ColorIndex = 1
Set c = ws.UsedRange.Find("a", lookat:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
FirstAddress = c.Address
Else
GoTo SkipWs
End If
Do
c.Font.ColorIndex = 3
Set c = ws.Cells.FindNext(c)
Loop Until c Is Nothing Or c.Address = FirstAddress
Set c = Nothing
SkipWs:
On Error GoTo 0
Next
Application.ScreenUpdating = True
Set c = Nothing
End Sub
It will search all sheets and find all references of the value in the Find method (in this case, the "a"). Adapt to suit your InputBox if desired.