Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cells.find & stopping the search
I am using a macro based on the cells.find feature, and I want to make
it stop searching at a certain row. Otherwise, it always finds the specific value back at the place where I entered it after the search loops through the whole column. Here is the code I have: Sub Look_Here1() Dim FoundCell As Range Dim WhatFor As Variant WhatFor = ActiveSheet.Cells(7, 2).Value Set FoundCell = Cells.Find(What:=WhatFor, after:=ActiveCell, _ SearchDirection:=xlNext, searchorder:=xlByRows, _ MatchCase:=False) FoundCell.Offset(0, -1).Select ActiveCell.FormulaR1C1 = "X" Selection.Offset(0, 4).Select End Sub Is there anything I can add to that to make it stop at, say, B990,? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
cells.find & stopping the search
Thanks.
I only need to find a single instance of the query, since reasonably it should only be in the list once. Ideally, when I solve this problem, I plan to insert a message box or some more code based on an "If..Then..Else" statement, but I can't do that until I make the cells.find code stop searching somewhere along the way. I'll definitely look at the FINDNEXT, though. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
cells.find & stopping the search
To restrict the range searched by the Find method, don't use cells (as Don
suggested). You can restrict the range searched, for example Range("A1:B990").Find will only search A1:B990. "michaelberrier" wrote: Thanks. I only need to find a single instance of the query, since reasonably it should only be in the list once. Ideally, when I solve this problem, I plan to insert a message box or some more code based on an "If..Then..Else" statement, but I can't do that until I make the cells.find code stop searching somewhere along the way. I'll definitely look at the FINDNEXT, though. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
cells.find & stopping the search
So, at what point in the code shown above would I put the
Range(XX).Find? And what would I leave out? thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
cells.find & stopping the search
Sub Look_Here1()
Dim FoundCell As Range Dim WhatFor As Variant WhatFor = ActiveSheet.Cells(7, 2).Value 'or columns(3).find Set FoundCell = range("c1:x500").Find(What:=WhatFor, after:=ActiveCell, _ SearchDirection:=xlNext, searchorder:=xlByRows, _ MatchCase:=False) FoundCell.Offset(0, -1)= "X" foundcell.Offset(0, 4).Select' or do something else -- Don Guillett SalesAid Software "michaelberrier" wrote in message oups.com... So, at what point in the code shown above would I put the Range(XX).Find? And what would I leave out? thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i build a search table in excel | Excel Worksheet Functions | |||
build a search tool in excel | Excel Discussion (Misc queries) | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) | |||
Cells.Find , why it DOESN'T search ? help | New Users to Excel | |||
FAQ Spreadsheet with search function | Excel Discussion (Misc queries) |