I have a workbook with 5 sheets in. When I recorded a macro using Find all
worked well. But when I come to run the macro I get a run time error 91. This
is the Macro,
Sheets("ft mid - small cap").Select
Cells.Find(What:="al_", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
_
False, SearchFormat:=False).Activate
I think that when the worksheet named is active it works fine, but I would like
to search the whole workbook, as later the What will change. I recall trying to
do this before and seem to remember using the 'with statement'. Please can
anybody advise.
Regards Robert

Robert

Could you want something like :

Option Explicit

Sub FindInAllSheets()

Dim FindValue As String
Dim oSht As Worksheet
Dim FoundCell As Range
Dim FirstAddr As String

FindValue = "al_"

For Each oSht In ActiveWorkbook.Worksheets
With oSht.Cells
Set FoundCell = .Find(What:=FindValue, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
Do
' your code here
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And
FoundCell.Address < FirstAddr
End If
End With
Next 'oSht

End Sub


---
Message posted from http://www.ExcelForum.com/

Thankyou for your advise.
Regards Robert