3D FIND

PROBLEM:

"The normal FIND function (CTRL-F) only searches the active sheet unless you manually set the workbook flag in advanced options. Can a macro be set to automatically search all worksheets for a text string, then jump to it if found?"

SPECIFICATION:

  1. Searches all sheets for the string
  2. Uses a popup interface
  3. Loops when search string is not found
Option ExplicitPublic MyVal As StringSub SuperFIND()'Author: Jerry Beaucaire'Date: 8/13/2010'Summary: Searches all sheets for any text string, activates when foundDim ws As WorksheetDim vFIND As RangeStartOver:MyVal = Application.InputBox("Enter the contact name to find", "Find In All Sheets", MyVal, Type:=2)If MyVal = "False" Then Exit SubOn Error Resume Next For Each ws In Worksheets Set vFIND = ws.Cells.Find(MyVal, LookIn:=xlValues, LookAt:=xlPart) If Not vFIND Is Nothing Then ws.Activate vFIND.Select Set vFIND = Nothing Exit Sub End If Next ws MsgBox "Contact not found"GoTo StartOverEnd Sub