Visual Basic (VBA) code to find text in any Excel worksheet

I've always been baffled that Excel's Find function only works on the current Worksheet. How hard would it be for Microsoft to search across all sheets in the Workbook?

It shouldn't be too hard, because I figured out how to do it in Visual Basic. You'll need to understand some VBA to get this inserted into your environment (recommendation: record a macro to your Personal Workbook, open the VB Editor, and then paste this subroutine into the Modules section of your Personal Workbook), but it should work as is. I tested it in Excel 2003 for XP and Excel version "X" for Mac OS X.

---

Sub FindEverywhere()
   ' Prompt the user for the searchText
   Dim searchText As String
   searchText = InputBox("Find what:", "Search All Worksheets")
  
   ' If the searchText is NOT an empty string, begin the search
   If Not searchText = "" Then
       Dim r As Range
       Dim findNext  As Integer
       findNext = vbYes
      
       Dim i As Integer
       ' Use For loop, not a For Each loop, as it's the only way to handle hidden/macro sheets!
       For i = 1 To Sheets.Count
       ' Loop through each sheet in the workbook.  This does NOT change the activate/visible sheet.
       ' That will come later if we're still searching (findNext=vbYes).  I'd prefer a method to
       ' "break" out of the For loop, but VBA doesn't have one?
           If Sheets.Item(i).Visible = xlSheetVisible Then
               ' This sheet is visible, so let's work with it (you HAVE to skip macro sheets, so just skip all hidden sheets)
               Dim sheet As Worksheet
               Set sheet = Sheets.Item(i) ' don't do this until you're sure this sheet is Visible!
              
               ' The firstFind and looped vars are used to determine if we've looped thru every find on this page.
               Dim firstFoundCell As String
               firstFoundCell = ""
               Dim looped As Boolean
               looped = False
              
               Do While findNext = vbYes And Not looped
                   ' This is necessary to search on the right sheet (?), but it also means we'll switch to every sheet,
                   ' including ones which do NOT contain searchText.  So, if you search the whole Workbook, you'll end up looking
                   ' at the last sheet, whether or not it contains the searchText.  :-(
                   Sheets(sheet.Name).Select
                  
                   ' Here's the search!
                   Set r = Cells.Find(searchText, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False)
                   If r Is Nothing Then
                       ' Nothing was found.  Exit this WHILE loop (the FOR loop will still go to the next sheet).
                       Exit Do
                   Else
                       r.Activate ' Activate (select) the cell that was found
                       ' Check/Set the firstFoundCell and the looped variable.
                       If firstFoundCell = "" Then
                           sheet.Activate
                           firstFoundCell = r.Address
                       Else
                           ' We've found at least one cell already.  Check to see if we've looped back to the firstFoundCell.
                           If r.Address = firstFoundCell Then
                               looped = True
                           Else
                           End If
                       End If ' end of checking for firstFoundCell
                      
                       ' If we haven't looped around, prompt the user to findNext.  (If we have looped, the WHILE loop will finish, causing the FOR loop to go to the next sheet.)
                       If Not looped Then
                               findNext = MsgBox("Find Next?", vbYesNo)
                       End If
                   End If
               Loop ' end of Do While loop
           End If
       Next i
      
       If findNext = vbYes Then
           MsgBox ("No more matches found!")
       End If
   End If
End Sub

---

As always, let me know if you have any comments or questions!