Excel 2019: Range.Find(Number) Trouble

[Spielberg]

Hello all excel gurus.

Using Excel 2019 VBA, just trying to do a simple find and select of a cell.

Trying a find a standard numerical value within a range.

For this example, let's say the range for the number to find contains 123.

"NumberList" is a named range covering B49:S72



N = Range("AV5").Value

Set Num2Find = Range("NumberList").Find(What:=N)


Num2Find returns "Nothing"

Then I tried



Set Num2Find = Range("NumberList").Find(What:=Range("AV5").Value)


Num2Find returns "Nothing"

Then I tried



Set Num2Find = Range("NumberList").Find(What:=124)


Num2Find returns "Nothing"

I really don't know why! I just want it to select the cell once it find it. The number definitely exists.

Can someone help please?

Thank you in advance!

Mike

[snb]

It doesn't exist in the worksheet.

[SamT]

Post # 2 or something is broken. Maybe Numberlist is not on the same sheet that the code is on? Is NumberList a Workbook Name or a Sheet Specific Name? One Of the reasons I, personally, never use Named Ranges in my code.

Alternative:
Sub t()
n = 124

For each Cel in Range("B49:S72")
If Cel = n then
Set NumToFind = Cel
Exit For
End If
Next
NumToFind.Select
MsgBox NumToFind.Address
End Sub

Final test: What Cel/Range is the Number (124) in?

Sub t()
MsgBox Range("???") = 124
End Sub

[p45cal]

.FIND has a whole bunch of arguments (LookIn, LookAt, SearchFormat and more) which you aren't specifying - those arguments can be 'remembered' from previous uses of .Find and from using the user interface find (Ctrl+F).
Best attach a workbook with the problem.

[Spielberg]

Thank you Mr. SamT! That was PERFECT!

I even change the range to my named range of "NumberList" and it still worked perfectly!

You sir, are a genius.

Thank you!

Mike

[SamT]

:thumb