Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restricted Cells | Excel Worksheet Functions | |||
Restricted Range in Auto filter | Excel Discussion (Misc queries) | |||
Password restricted single macro and button | Excel Discussion (Misc queries) | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) | |||
Protecting single cells or a range | Excel Discussion (Misc queries) |