Limit find range
 

Is there a way to limit the amount of cells excel will
search in to find a specific value. I have tried this,

Set celltofind = Cells.Range("A1:A50").Find(What:="No
Match", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

But get an error :o(

If I do not include the .Range("A1:A50") to the above.
The spreadsheet will keep looking until it finds another
match and thus mess the way I track other stuff. I have a
spreadsheet that seperates canadian customers from US
customers and I would like to keep the finds seperate if
possible.

Thanks
Pete W

Limit find range
 

You can define the sheet to
search

Set wks = ActiveSheet (or whatever sheet you want)
and the range to search
Set rngToSearch = wks.Range("B2:B10") 'Or whatever range you want....


Public Sub InsertRows()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Range("B2:B10")
Set rngFound = rngToSearch.Find("Line", , xlValues, xlPart)

If Not rngFound Is Nothing Then
Set rngFirst = rngFound
msgbox rngfound.address
Do
Set rngFound = rngToSearch.FindNext(rngFound)
msgbox rngfound.address
Loop Until rngFound.Address = rngFirst.Address
End If

End Sub

HTH


"Pete" wrote:

Is there a way to limit the amount of cells excel will
search in to find a specific value. I have tried this,

Set celltofind = Cells.Range("A1:A50").Find(What:="No
Match", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

But get an error :o(

If I do not include the .Range("A1:A50") to the above.
The spreadsheet will keep looking until it finds another
match and thus mess the way I track other stuff. I have a
spreadsheet that seperates canadian customers from US
customers and I would like to keep the finds seperate if
possible.

Thanks
Pete W

Limit find range
 

Pete

What is the error? The only way you'll get an error on that is if the
ActiveCell isn't in A1:A50. The range you supply for the After argument
must be in the range for the Find. If you want to find all occurences, just
omit the After argument.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Pete wrote:
Is there a way to limit the amount of cells excel will
search in to find a specific value. I have tried this,

Set celltofind = Cells.Range("A1:A50").Find(What:="No
Match", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

But get an error :o(

If I do not include the .Range("A1:A50") to the above.
The spreadsheet will keep looking until it finds another
match and thus mess the way I track other stuff. I have a
spreadsheet that seperates canadian customers from US
customers and I would like to keep the finds seperate if
possible.

Thanks
Pete W