findnext question

[Gary Keramidas]

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

 

[Peter T]

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

 

[Dave Peterson]

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.

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 Keramidas]

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

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.

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

 

[Dave Peterson]

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.

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

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.

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

 

[Peter T]

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

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.

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

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