Search for a value

[StevieC9]

Hi,
I have a workbook with multiple sheets along the bottom. Within each sheet is a list of policy numbers.
Can anyone supply a simple code which will provide a message box titled 'Search' and when I input a policy number and press enter it will search all sheets and if it locates the correct policy number, it goes directly to that particular cell.

Your help would be appreciated!

[JBeaucaire]

This should do it for you:

Option Explicit

Sub SearchALLSheets()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      10/17/2010
'Summary:   A string is searched for in all sheets, the value searched must be
'           the whole cell value, partial strings are not matched

Dim MyVal As String
Dim ws As Worksheet
Dim vFind As Range

MyVal = Application.InputBox("Enter Policy Number to locate:", "Policy Locator", 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:=xlWhole, MatchCase:=False)
    If Not vFind Is Nothing Then
        ws.Activate
        vFind.Select
        Exit Sub
    End If
Next ws

MsgBox "Policy " & MyVal & " was not found."

End Sub

[JBeaucaire]

There's a more robust version of this concept published on my site.

'3D Find
I have a macro that may be "ready to use" for searching all sheets in an Excel file for any string and jumping to that cell automatically if it is found.

3D FIND

[royUK]

Why have data split like that?

Option Explicit

Sub SearchAll()
Dim wSht As Worksheet
Dim rCl As Range
Dim sFind As String

sFind = InputBox("Enter policy number")
If sFind <> "" Then
For Each wSht In ThisWorkbook.Worksheets
Set rCl = wSht.UsedRange.Find(sFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not rCl Is Nothing Then
Application.GoTo rCl, True
Exit For
End If
Next wSht
Else: MsgBox "You must enter a policy number", vbCritical, "Input required"
Exit Sub
End If
End Sub