VBA help required with 'Find' command

MacGrizz

New Member
Joined
Sep 20, 2005
Messages
39
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

MacGrizz

New Member
Joined
Sep 20, 2005
Messages
39
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I don't know what would cause that. It worked for me even if the sheet was hidden.

Anyway, try this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,486
Members
412,669
Latest member
nickyon1
Top