Cells.find restricted to a single range??

[Steff_DK]

I have the below code to search for a number in my sheet.

However, I only want to search the range "rngtest", and if the numbe
does not exist in that range, it should return only a msgbox saying n
such entry.

What should be changed???


Dim iSpecific As integer
Cells.Find(What:=iSpecific, After:=Range("A1"), LookIn:=xlFormulas
LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext
MatchCase _
:=False, SearchFormat:=False).Activate

Cheers

Stef

 

[Pete McCosh]

Steff,
replace "Cells.Find" with the following, if "rngtest" is a
variable in your code:

rngtest.find

If "rngtest" is a named range in the workbook:

Range("rngtest").find

Cheers, Pete.

 

[Steff_DK]

What if there's no such entry in the range?

I get runtime error if I search for a number that isn't in the range..

 

[Chip Pearson]

Steff,

Try writing your code like

Dim FoundCell As Range
Set FoundCell = RngTest.Find(...)
If FoundCell Is Nothing Then
' no value found in RngTest
Else
FoundCell.Activate
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Steff_DK]

I get error 424 object req'd...

Dunno why :-/


Dim FoundCell As Range
Set FoundCell = Range("case").Find(What:=iSpecific, After:=Range("A1")
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext
MatchCase _
:=False, SearchFormat:=False).Activate

If FoundCell Is Nothing Then
MsgBox ("Number doesn't exist.")
Else
FoundCell.Activat

 

[Juan Pablo Gonzalez]

Remove the .Activate at the end of your .Find() method...