VBA help required with 'Find' command

[MacGrizz]

Hope someone can help.

I need a macro that will take a user-entered serial number & then use the Find command to search entire workbook for a match. I would then like to store the sheet name and/or cell reference of the first match.

Can this be done?

Rgds,
MacGrizz

 

[Andrew Poulsom]

Here is a starter:

http://www.mrexcel.com/board2/viewtopic.php?t=56844

 

[MacGrizz]

Encountered an error with this macro.

Sub Test()
Dim What As String
Dim sht As Worksheet
Dim Found As Range
Dim FirstAddress As String
Dim Response
What = InputBox("Search for :")
If What = "" Then Exit Sub
For Each sht In Worksheets
sht.Activate
Set Found = sht.Cells.Find(What)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Activate
Response = MsgBox("Continue?", vbYesNo + vbQuestion)
If Response = vbNo Then Exit Sub
Set Found = Cells.FindNext(After:=ActiveCell)
If Found.Address = FirstAddress Then Exit Do
Loop
End If
Next sht
MsgBox "Search Ended!"
End Sub

Error message said "Activate method of range class failed".

Any further ideas on what might be causing this.

Ideally, I want to be able to store the sheet name & cell reference of the first found result.

Thanks.

 

[Andrew Poulsom]

I don't know what would cause that. It worked for me even if the sheet was hidden.

Anyway, try this:

Sub Test()
    Dim What As String
    Dim sht As Worksheet
    Dim Found As Range
    What = InputBox("Search for :")
    If What = "" Then Exit Sub
    For Each sht In Worksheets
        Set Found = sht.Cells.Find(What)
        If Not Found Is Nothing Then
            MsgBox sht.Name & " " & Found.Address(False, False)
            Exit Sub
        End If
    Next sht
    MsgBox What & " not found."
End Sub

 

[MacGrizz]

Superb. This works fine.

Thanks a lot!