Search Functions‎ > ‎

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

CODE

Option Explicit
Public MyVal As String

Sub SuperFIND()
'Author:    Jerry Beaucaire
'Date:      8/13/2010
'Summary:   Searches all sheets for any text string, activates when found
Dim ws      As Worksheet
Dim vFIND   As Range

StartOver:
MyVal = Application.InputBox("Enter the contact name to find", "Find In All Sheets", MyVal, Type:=2)
If MyVal = "False" Then Exit Sub
On 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 StartOver

End Sub



Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!

Comments