software

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 = quot;Xquot;
Selection.Offset(0, 4).Select
End Sub

Is there anything I can add to that to make it stop at, say, B990,?
Thanks.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
gt; ActiveCell.FormulaR1C1 = quot;Xquot;
gt; Selection.Offset(0, 4).Select
gt;
FoundCell.Offset(0, -1) = quot;Xquot;
foundcell.Offset(0, 4)=quot;?quot;

--
Don Guillett
SalesAid Software

quot;michaelberrierquot; gt; wrote in message oups.com...
gt;I am using a macro based on the cells.find feature, and I want to make
gt; it stop searching at a certain row. Otherwise, it always finds the
gt; specific value back at the place where I entered it after the search
gt; loops through the whole column. Here is the code I have:
gt; Sub Look_Here1()
gt; Dim FoundCell As Range
gt; Dim WhatFor As Variant
gt; WhatFor = ActiveSheet.Cells(7, 2).Value
gt;
gt; Set FoundCell = Cells.Find(What:=WhatFor, after:=ActiveCell, _
gt; SearchDirection:=xlNext, searchorder:=xlByRows,
gt; _
gt; MatchCase:=False)
gt;
gt; FoundCell.Offset(0, -1).Select
gt; ActiveCell.FormulaR1C1 = quot;Xquot;
gt; Selection.Offset(0, 4).Select
gt;
gt;
gt;
gt; End Sub
gt;
gt; Is there anything I can add to that to make it stop at, say, B990,?
gt; Thanks.
gt;
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 quot;If..Then..Elsequot; 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.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(quot;A1:B990quot;).Find

will only search A1:B990.quot;michaelberrierquot; wrote:

gt; Thanks.
gt; I only need to find a single instance of the query, since reasonably it
gt; should only be in the list once.
gt;
gt; Ideally, when I solve this problem, I plan to insert a message box or
gt; some more code based on an quot;If..Then..Elsequot; statement, but I can't do
gt; that until I make the cells.find code stop searching somewhere along
gt; the way. I'll definitely look at the FINDNEXT, though.
gt; Thanks.
gt;
gt;

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

thanks.Sub Look_Here1()
Dim FoundCell As Range
Dim WhatFor As Variant
WhatFor = ActiveSheet.Cells(7, 2).Value
'or columns(3).find
Set FoundCell = range(quot;c1:x500quot;).Find(What:=WhatFor, after:=ActiveCell,
_
SearchDirection:=xlNext, searchorder:=xlByRows,
_
MatchCase:=False)

FoundCell.Offset(0, -1)= quot;Xquot;
foundcell.Offset(0, 4).Select' or do something else--
Don Guillett
SalesAid Software

quot;michaelberrierquot; gt; wrote in message oups.com...
gt; So, at what point in the code shown above would I put the
gt; Range(XX).Find? And what would I leave out?
gt;
gt; thanks.
gt;