Use Range Name with 'Find'
 

And you want to select the first cell that's found?

Dim foundcell As Range
dim myVals as Variant
dim iCtr as long

myVals = array(22,13,44)
With ActiveSheet
for ictr = lbound(myVals) to ubound(myvals)
'might as well use that named range
Set foundcell = .Range("Priced").Find(What:=myvals(ictr))
If foundcell Is Nothing Then
MsgBox "not found"
Else
foundcell.Select
exit for
End If
next ictr
End With

ps. I think that you would be much better served by supplying all the parms to
the .find command. .Find will remember the settings that were last used--either
in code or by the user.

For example, if you want xlwhole, then specify it. The user may have changed
the parm to xlPart. And your code may not do what you want.

ps. Watch out for typos. I composed in the email window.

Paige wrote:

I have a range name ('Priced') that includes multiple values (like 22, 13,
44, etc.), and want to be able to find any of the values included in this
range name. What I have below finds only the number '22'; how can I modify
it to find any of the numbers found in the range name? Have tried numerous
ways to do this, but am obviously missing the correct syntax. P.S. The
range name is on another sheet within the same file.

Dim foundcell As Range
With ActiveSheet
Set foundcell = .Range("o3:o65536").Find(What:=22)
If foundcell Is Nothing Then
MsgBox "not found"
Else
foundcell.Select
End If
End With

Thanks....Paige

--

Dave Peterson

Use Range Name with 'Find'
 

You may want to comment that "msgbox" line. It can become irritatin'.

Or you may want to make it more meaningful:
msgbox myvals(ictr) & " wasn't found"



Paige wrote:

Hi, Dave...yes. Thanks!

"Dave Peterson" wrote:

And you want to select the first cell that's found?

Dim foundcell As Range
dim myVals as Variant
dim iCtr as long

myVals = array(22,13,44)
With ActiveSheet
for ictr = lbound(myVals) to ubound(myvals)
'might as well use that named range
Set foundcell = .Range("Priced").Find(What:=myvals(ictr))
If foundcell Is Nothing Then
MsgBox "not found"
Else
foundcell.Select
exit for
End If
next ictr
End With

ps. I think that you would be much better served by supplying all the parms to
the .find command. .Find will remember the settings that were last used--either
in code or by the user.

For example, if you want xlwhole, then specify it. The user may have changed
the parm to xlPart. And your code may not do what you want.

ps. Watch out for typos. I composed in the email window.

Paige wrote:

I have a range name ('Priced') that includes multiple values (like 22, 13,
44, etc.), and want to be able to find any of the values included in this
range name. What I have below finds only the number '22'; how can I modify
it to find any of the numbers found in the range name? Have tried numerous
ways to do this, but am obviously missing the correct syntax. P.S. The
range name is on another sheet within the same file.

Dim foundcell As Range
With ActiveSheet
Set foundcell = .Range("o3:o65536").Find(What:=22)
If foundcell Is Nothing Then
MsgBox "not found"
Else
foundcell.Select
End If
End With

Thanks....Paige

--

Dave Peterson


--

Dave Peterson

Use Range Name with 'Find'
 

Good idea - will do! Thanks again; have a good evening.

"Dave Peterson" wrote:

You may want to comment that "msgbox" line. It can become irritatin'.

Or you may want to make it more meaningful:
msgbox myvals(ictr) & " wasn't found"



Paige wrote:

Hi, Dave...yes. Thanks!

"Dave Peterson" wrote:

And you want to select the first cell that's found?

Dim foundcell As Range
dim myVals as Variant
dim iCtr as long

myVals = array(22,13,44)
With ActiveSheet
for ictr = lbound(myVals) to ubound(myvals)
'might as well use that named range
Set foundcell = .Range("Priced").Find(What:=myvals(ictr))
If foundcell Is Nothing Then
MsgBox "not found"
Else
foundcell.Select
exit for
End If
next ictr
End With

ps. I think that you would be much better served by supplying all the parms to
the .find command. .Find will remember the settings that were last used--either
in code or by the user.

For example, if you want xlwhole, then specify it. The user may have changed
the parm to xlPart. And your code may not do what you want.

ps. Watch out for typos. I composed in the email window.

Paige wrote:

I have a range name ('Priced') that includes multiple values (like 22, 13,
44, etc.), and want to be able to find any of the values included in this
range name. What I have below finds only the number '22'; how can I modify
it to find any of the numbers found in the range name? Have tried numerous
ways to do this, but am obviously missing the correct syntax. P.S. The
range name is on another sheet within the same file.

Dim foundcell As Range
With ActiveSheet
Set foundcell = .Range("o3:o65536").Find(What:=22)
If foundcell Is Nothing Then
MsgBox "not found"
Else
foundcell.Select
End If
End With

Thanks....Paige

--

Dave Peterson


--

Dave Peterson