Trying to limit .Find to a specific range
 

I am trying to find occurances of value within a range of cells.
Could someone help me with this code?

tia

For Each rngMyCell In rngMyRange
With Cells
Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngMyCell Is Nothing Then
strFirstAddress = rngMyCell.Address
Do
strData = rngMyCell.Formula
lCountValueReplaces = lCountValueReplaces + 1
Set rngMyCell = .FindNext(rngMyCell)
Loop While Not rngMyCell Is Nothing And ngMyCell.Address
< strFirstAddress
End If
End With
Next

Trying to limit .Find to a specific range
 

Hi:

Instead of:

With Cells

use:

With Range("A1:Z100") 'or whatever

Regards,

Vasant.

wrote in message
...
I am trying to find occurances of value within a range of cells.
Could someone help me with this code?

tia

For Each rngMyCell In rngMyRange
With Cells
Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngMyCell Is Nothing Then
strFirstAddress = rngMyCell.Address
Do
strData = rngMyCell.Formula
lCountValueReplaces = lCountValueReplaces + 1
Set rngMyCell = .FindNext(rngMyCell)
Loop While Not rngMyCell Is Nothing And ngMyCell.Address
< strFirstAddress
End If
End With
Next

Trying to limit .Find to a specific range
 

Bill,

Just change
With Cells
to
With Range("A1:A10")

or whatever range you want to search.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


wrote in message
...
I am trying to find occurances of value within a range of cells.
Could someone help me with this code?

tia

For Each rngMyCell In rngMyRange
With Cells
Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngMyCell Is Nothing Then
strFirstAddress = rngMyCell.Address
Do
strData = rngMyCell.Formula
lCountValueReplaces = lCountValueReplaces + 1
Set rngMyCell = .FindNext(rngMyCell)
Loop While Not rngMyCell Is Nothing And ngMyCell.Address
< strFirstAddress
End If
End With
Next

Trying to limit .Find to a specific range
 

Dim rngMyRange as Range
Dim rngMyCell as Range
Dim strFirstAddress as String
Dim strEdit_Find as String
Dim strData as String
Dim lCountValueReplaces as Long
With rngMyRange
Set rngMyCell = .Find(What:=strEdit_Find, _
After:=rngMyRange(rng.MyRange.count), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngMyCell Is Nothing Then
strFirstAddress = rngMyCell.Address
Do
strData = strData & rngMyCell.Formula & vbNewLine
lCountValueReplaces = lCountValueReplaces + 1
Set rngMyCell = .FindNext(rngMyCell)
Loop While Not rngMyCell Is Nothing And _
rngMyCell.Address < strFirstAddress
End If
End With
msgbox strData

Your not doing any replacing, so I am not sure what you want out of
lcountValueReplaces

It will count the number cells found containing strEdit_Find

--
Regards,
Tom Ogilvy


wrote in message
...
I am trying to find occurances of value within a range of cells.
Could someone help me with this code?

tia

For Each rngMyCell In rngMyRange
With Cells
Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngMyCell Is Nothing Then
strFirstAddress = rngMyCell.Address
Do
strData = rngMyCell.Formula
lCountValueReplaces = lCountValueReplaces + 1
Set rngMyCell = .FindNext(rngMyCell)
Loop While Not rngMyCell Is Nothing And ngMyCell.Address
< strFirstAddress
End If
End With
Next

Trying to limit .Find to a specific range
 

Thanks, that worked perfectly


On Sun, 2 Nov 2003 15:42:14 -0600, "Chip Pearson"
wrote:

Bill,

Just change
With Cells
to
With Range("A1:A10")

or whatever range you want to search.

Trying to limit .Find to a specific range
 

If that is all you changed, you are wasting a lot of time in your code. You
are also changing your loop variable within the loop which is not
recommended, but you don't need the loop anyway.


--
Regards,
Tom Ogilvy


wrote in message
...
Thanks, that worked perfectly


On Sun, 2 Nov 2003 15:42:14 -0600, "Chip Pearson"
wrote:

Bill,

Just change
With Cells
to
With Range("A1:A10")

or whatever range you want to search.