VBA Help with Looping through all Sheets in Workbook

[Latchmaker]

I have a code that I want to run against any sheet within an active workbook however the find function in VBA limits the search to the active sheet not all sheets....Please Help this is my code.

Sub FindX()

On Error GoTo MyErrorHandler

Cells.Find(What:="x:", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Exit Sub
MyErrorHandler:
Msg = "No Links to the X:Drive Found...Have A Good Day!!"
MsgBox Msg
Exit Sub
End Sub

I want the code to loop through all sheets and if there is no Link to X: then return the Msg...if there is a link I want to go to that cell.

 

[jbeaucaire]

This should do it:

Option Explicit

Sub FindX()
Dim ws As Worksheet, rFind As Range
On Error Resume Next

For Each ws In Worksheets
    Set rFind = ws.Cells.Find(What:="x:", LookIn:=xlFormulas, _
                                        LookAt:=xlPart, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False, _
                                        SearchFormat:=False)
    If Not rFind Is Nothing Then
        ws.Select
        rFind.Select
        Exit Sub
    End If
Next ws

MsgBox "No Links to the X:Drive Found...Have A Good Day!!"
End Sub

 

[Norie]

VBA itself doesn't limit the search to the active sheet.

The reason that is happening is because that's what you are telling it to do.

You mention a loop in the thread title but I see no loop in your code.

Perhaps something like this will work.

Option Explicit
Sub FindX()
Dim ws As Worksheet
Dim rng As Range
 
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Set rng = .Cells.Find(What:="x:", After:=.Range("A1"), LookIn:=xlFormulas, LookAt:= _
                                  xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If rng Is Nothing Then
                MsgBox "No links to X: drive found on worksheet " & .Name
            End If
        End With

    Next ws
    
End Sub