Trouble with FIND

[utuber]

Hi All,
I'm having trouble with using FIND and I've finally given up. I just know this will be easy for most everyone else but not me.

The code
Sub Find_Cell_In_Menu()

Dim s As String
Dim rng As Range

Worksheets("Menu").Activate
s = Range("M3") ' Example of M3 value = 9008228

Set rng = Sheet1.Columns("B:B").Find(What:=s).Select

End Sub

I have had an aberration of the above which worked one day but not the next. I then read that somehow the search value can change by not clearing some value etc.
I didn't save the version that worked for a short time thinking I can tweak the code and fix it but NO I didn't fix it. Currently I'm getting 'Object Required' Error and haven't been able to fix the Error.
Basically it retrieves the s Value then attempts to have the ActiveCell land on the s Value located in Column B.
Can somebody please help this poor 75 year old from going crazy
Thank you one and all..
'

 

[Akuini]

Try this:

Sub Find_Cell_In_Menu()

Dim s As String
Dim rng As Range

s = Worksheets("Menu").Range("M3") ' Example of M3 value = 9008228

Set rng = Sheet1.Columns("B:B").Find(What:=s, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not rng Is Nothing Then
    rng.Select
Else
    MsgBox "Can't find: " & s
End If

End Sub

 

[jolivanes]

Or even so.

Sub Maybe_So()
Worksheets(1).Activate
    Columns(2).Find(Range("M3").Value).Select
End Sub

 

[utuber]

Try this:

Sub Find_Cell_In_Menu()

Dim s As String
Dim rng As Range

s = Worksheets("Menu").Range("M3") ' Example of M3 value = 9008228

Set rng = Sheet1.Columns("B:B").Find(What:=s, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not rng Is Nothing Then
    rng.Select
Else
    MsgBox "Can't find: " & s
End If

End Sub

Thank you very much both Akuini and jolivanes.
Both codes worked as I required.
Cheers
Peter

 

[Akuini]

I have had an aberration of the above which worked one day but not the next.

It's probably because one or more parameter has changed when you run the code & the changes do not match your criteria. It's a good practice to set the Find parameter as needed, that's why in my example above I set all the parameter, in this part:

Set rng = Sheet1.Columns("B:B").Find(What:=s, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

Check this article for better understanding:

Excel VBA Find - A Complete Guide - Excel Macro Mastery

This post provides a complete guide to using the Excel VBA Find function. It contains code examples for almost anything y ou want to do with VBA Find.

excelmacromastery.com

Important Note about Find Parameters​

Keep the following in mind as it can cause a lot of frustration when using Find.

As you can see from the table most of the VBA Find parameters are optional. As we said earlier, if you don’t set a Find parameter it uses the existing setting.

For example, if you set the LookIn parameter to xlComments, it will search for a value in comments only. The next time you run Find(either from the Dialog or from VBA) the existing LookIn setting will be Comments.