cells.find & stopping the search

[michaelberrier]

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.

 

[Don Guillett]

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)="?"

 

[michaelberrier]

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.

 

[JMB]

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]

So, at what point in the code shown above would I put the
Range(XX).Find? And what would I leave out?

thanks.

 

[Don Guillett]

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