Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Findnext problem | Excel Programming | |||
Findnext | Excel Discussion (Misc queries) | |||
FindNext | Excel Programming | |||
FindNext | Excel Programming |