VBA Help with Looping through all Sheets in Workbook

Latchmaker

Active Member
Joined
Sep 30, 2005
Messages
308
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
This should do it:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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.:)
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,466
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top