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.

cells.find & stopping the search
 

IF?? you are trying to find more than one instance of the whatfor then I
suggest you use FINDNEXT. There is an excellent example in the vba help
index. If possible do NOT use cells. Use the column(s) or range involved.
Saves time.

Also, you need NOT select anything.

FoundCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "X"
Selection.Offset(0, 4).Select

FoundCell.Offset(0, -1) = "X"
foundcell.Offset(0, 4)="?"




--
Don Guillett
SalesAid Software

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

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.

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.

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.

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.