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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,737
Messages
6,103,629
Members
447,873
Latest member
dolphin_tlp

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
Back
Top