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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,499
Messages
5,832,067
Members
430,109
Latest member
tinezi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top