Cells.find restricted to a single range??
 

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

--
Message posted from http://www.ExcelForum.com

Cells.find restricted to a single range??
 

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.

-----Original Message-----
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 number
does not exist in that range, it should return only a
msgbox saying no
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

Steff

Cells.find restricted to a single range??
 

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..

--
Message posted from http://www.ExcelForum.com

Cells.find restricted to a single range??
 

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 " wrote in
message ...
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...


---
Message posted from http://www.ExcelForum.com/

Cells.find restricted to a single range??
 

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

--
Message posted from http://www.ExcelForum.com

Cells.find restricted to a single range??
 

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

--
Regards,

Juan Pablo González

"Steff_DK " wrote in message
...
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.Activate


---
Message posted from http://www.ExcelForum.com/