type mismatch here!

[J_J]

Hi,
the below code (which was discussed here some time ago) finds and highlights
all occurances of watched string on active sheet. How can I put a loop to it
so that it will do the same one after the other one for all worksheets in
the workbook, and highlights all of them?.
TIA
J_J



Option Explicit
Sub find_all()

Dim myRng As Range
Dim FoundCell As Range
Dim AllCells As Range
Dim FirstAddress As String
Dim whatToFind As String
Dim sh As Worksheet

whatToFind = "abc"

Set myRng = ActiveSheet.Range("A1:IV3000")

With myRng
Set FoundCell = .Cells.Find(what:=whatToFind, _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
If AllCells Is Nothing Then
Set AllCells = FoundCell
Else
Set AllCells = Union(FoundCell, AllCells)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With

If AllCells Is Nothing Then
MsgBox whatToFind & " wasn't found"
Else
AllCells.Select
End If
End Sub

 

[Dave Peterson]

Wrapping it in a loop worked ok for me:

Option Explicit
Sub find_all()

Dim myRng As Range
Dim FoundCell As Range
Dim AllCells As Range
Dim FirstAddress As String
Dim whatToFind As String
Dim wks As Worksheet

whatToFind = "abc"

For Each wks In ActiveWorkbook.Worksheets
wks.Select
Set AllCells = Nothing

Set myRng = wks.Range("A1:IV3000")

With myRng
Set FoundCell = .Cells.Find(what:=whatToFind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
If AllCells Is Nothing Then
Set AllCells = FoundCell
Else
Set AllCells = Union(FoundCell, AllCells)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With

If AllCells Is Nothing Then
MsgBox whatToFind & " wasn't found on worksheet: " & wks.Name
Else
AllCells.Select
End If
Next wks
End Sub

 

[J_J]

Worked perfectly well here too...
Thanks Dave.
J_J