findnext question
 

in the help, they use firstaddress = c.address so the code doesn't loop
continuously

if i use similar, it generates an error, but my code seems to run fine, what's
the difference?

With ws.Range("C1:C" & lastRow)
Set rngFound = .Find(What:="<", LookIn:=xlValues)
Do
If Not rngFound Is Nothing Then
rngFound.Value = Replace(Replace(rngFound.Value, "<", "-", 1), "",
_
"")
rngFound.NumberFormat = "#,##0_);[Red](#,##0.00)"
Set rngFound = .FindNext(rngFound)
End If
Loop While Not rngFound Is Nothing
End With


--


Gary

findnext question
 

Hi Gary,

In your example you replace the search string in each found cell, so
eventually the search string will not be found and rngFound = (ie Is)
nothing. If you comment your replace line your code would run continuously
looping back to the first found(assuming a found) unless you amend to
something similar to the help example.

Regards,
Peter T

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
in the help, they use firstaddress = c.address so the code doesn't loop
continuously

if i use similar, it generates an error, but my code seems to run fine,
what's
the difference?

With ws.Range("C1:C" & lastRow)
Set rngFound = .Find(What:="<", LookIn:=xlValues)
Do
If Not rngFound Is Nothing Then
rngFound.Value = Replace(Replace(rngFound.Value, "<", "-", 1),
"",
_
"")
rngFound.NumberFormat = "#,##0_);[Red](#,##0.00)"
Set rngFound = .FindNext(rngFound)
End If
Loop While Not rngFound Is Nothing
End With


--


Gary

findnext question
 

This line from VBAs help:

Loop While Not c Is Nothing And c.Address < firstAddress

will blow up if c is nothing (since c.address will return an error).

If you weren't removing the seached value, then the .findnext() would loop until
it got to the first found cell and never be nothing.

Gary Keramidas wrote:

in the help, they use firstaddress = c.address so the code doesn't loop
continuously

if i use similar, it generates an error, but my code seems to run fine, what's
the difference?

With ws.Range("C1:C" & lastRow)
Set rngFound = .Find(What:="<", LookIn:=xlValues)
Do
If Not rngFound Is Nothing Then
rngFound.Value = Replace(Replace(rngFound.Value, "<", "-", 1), "",
_
"")
rngFound.NumberFormat = "#,##0_);[Red](#,##0.00)"
Set rngFound = .FindNext(rngFound)
End If
Loop While Not rngFound Is Nothing
End With

--

Gary

--

Dave Peterson

findnext question
 

ok, thanks to both for the explanation. so, in the help example for findnext, is
"Loop While Not c Is Nothing And c.Address < firstAddress"
needed? it's changing 2 to 5, so i would think that line isn't needed, either.

--


Gary


"Dave Peterson" wrote in message
...
This line from VBAs help:

Loop While Not c Is Nothing And c.Address < firstAddress

will blow up if c is nothing (since c.address will return an error).

If you weren't removing the seached value, then the .findnext() would loop
until
it got to the first found cell and never be nothing.

Gary Keramidas wrote:

in the help, they use firstaddress = c.address so the code doesn't loop
continuously

if i use similar, it generates an error, but my code seems to run fine,
what's
the difference?

With ws.Range("C1:C" & lastRow)
Set rngFound = .Find(What:="<", LookIn:=xlValues)
Do
If Not rngFound Is Nothing Then
rngFound.Value = Replace(Replace(rngFound.Value, "<", "-", 1),
"",
_
"")
rngFound.NumberFormat = "#,##0_);[Red](#,##0.00)"
Set rngFound = .FindNext(rngFound)
End If
Loop While Not rngFound Is Nothing
End With

--

Gary

--

Dave Peterson

findnext question
 

Worse than not needed. It causes a run time error for me.

But that example was changed from an earlier version. In an earlier version, it
changed colors--not values. When they changed the example, they broke it.

Gary Keramidas wrote:

ok, thanks to both for the explanation. so, in the help example for findnext, is
"Loop While Not c Is Nothing And c.Address < firstAddress"
needed? it's changing 2 to 5, so i would think that line isn't needed, either.

--

Gary

"Dave Peterson" wrote in message
...
This line from VBAs help:

Loop While Not c Is Nothing And c.Address < firstAddress

will blow up if c is nothing (since c.address will return an error).

If you weren't removing the seached value, then the .findnext() would loop
until
it got to the first found cell and never be nothing.

Gary Keramidas wrote:

in the help, they use firstaddress = c.address so the code doesn't loop
continuously

if i use similar, it generates an error, but my code seems to run fine,
what's
the difference?

With ws.Range("C1:C" & lastRow)
Set rngFound = .Find(What:="<", LookIn:=xlValues)
Do
If Not rngFound Is Nothing Then
rngFound.Value = Replace(Replace(rngFound.Value, "<", "-", 1),
"",
_
"")
rngFound.NumberFormat = "#,##0_);[Red](#,##0.00)"
Set rngFound = .FindNext(rngFound)
End If
Loop While Not rngFound Is Nothing
End With

--

Gary

--

Dave Peterson

--

Dave Peterson

findnext question
 

But that example was changed from an earlier version.

Here's the Find example as quoted similarly in both XL97 and XL2000 -

Find Method Example

This example finds all cells in the range A1:A500 on worksheet one that
contain the value 2, and then it makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Regards,
Peter T


"Dave Peterson" wrote in message
...
Worse than not needed. It causes a run time error for me.

But that example was changed from an earlier version. In an earlier
version, it
changed colors--not values. When they changed the example, they broke it.

Gary Keramidas wrote:

ok, thanks to both for the explanation. so, in the help example for
findnext, is
"Loop While Not c Is Nothing And c.Address < firstAddress"
needed? it's changing 2 to 5, so i would think that line isn't needed,
either.

--

Gary

"Dave Peterson" wrote in message
...
This line from VBAs help:

Loop While Not c Is Nothing And c.Address < firstAddress

will blow up if c is nothing (since c.address will return an error).

If you weren't removing the seached value, then the .findnext() would
loop
until
it got to the first found cell and never be nothing.

Gary Keramidas wrote:

in the help, they use firstaddress = c.address so the code doesn't
loop
continuously

if i use similar, it generates an error, but my code seems to run
fine,
what's
the difference?

With ws.Range("C1:C" & lastRow)
Set rngFound = .Find(What:="<", LookIn:=xlValues)
Do
If Not rngFound Is Nothing Then
rngFound.Value = Replace(Replace(rngFound.Value, "<",
"-", 1),
"",
_
"")
rngFound.NumberFormat = "#,##0_);[Red](#,##0.00)"
Set rngFound = .FindNext(rngFound)
End If
Loop While Not rngFound Is Nothing
End With

--

Gary

--

Dave Peterson

--

Dave Peterson