Wednesday, July 9, 2008

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. :-(

' 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
r.Activate ' Activate (select) the cell that was found
' Check/Set the firstFoundCell and the looped variable.
If firstFoundCell = "" Then
firstFoundCell = r.Address
' 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
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!