Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you test to break out of a find loop?
I want to use the following in a Do While Loop, but I don't know how to test
when the value I am searching for is not found. One additional piece of information. When I do hit on a cell that has this value I modify the cell so that it no longer has the value I am Do While (??????) Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate ... Test to see if value is found.... Note... If value is not found I know I can't activate a cell, but I don't know where the active cell will end up ..... Other Processing .... Loop -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you test to break out of a find loop?
Henry
I'm not trying to fob you off but have a look at the help file for find. I personally find it one of the best references for this type of problem! ;-) Henry Stock wrote in message ... I want to use the following in a Do While Loop, but I don't know how to test when the value I am searching for is not found. One additional piece of information. When I do hit on a cell that has this value I modify the cell so that it no longer has the value I am Do While (??????) Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate ... Test to see if value is found.... Note... If value is not found I know I can't activate a cell, but I don't know where the active cell will end up ..... Other Processing .... Loop -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you test to break out of a find loop?
Henry,
This should show you a technique On Error Resume Next Cells.Find(What:=":", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate If Err.Number < 0 Then MsgBox "Not Found" End If On Error GoTo 0 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Henry Stock" wrote in message ... I want to use the following in a Do While Loop, but I don't know how to test when the value I am searching for is not found. One additional piece of information. When I do hit on a cell that has this value I modify the cell so that it no longer has the value I am Do While (??????) Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate ... Test to see if value is found.... Note... If value is not found I know I can't activate a cell, but I don't know where the active cell will end up ..... Other Processing .... Loop -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you test to break out of a find loop?
I do not want to be impolite either, but yes... I did spend quite a bit of
time trying to find the answer in help, both local and online. I wasted probably at least few hours looking to no avail, so that is why I am asking here. I could go into detail about variants I tried. I tried to test for a result from the find, in many languages functions return a value, boolean or numeric to indicate sucess or failure. That did not seem to work. I tried testing cell addresses before and after to see if the ActiveCell remained the same or went back to an original location. That did not work. I did searches on patterns like "Cells.Find", "Find", and others. I browsed for functions... I looked for info on the Cells object to see if I could find data on the "Find" method.... I must be a poor searcher if it that easy to find. "Loomah" wrote in message ... Henry I'm not trying to fob you off but have a look at the help file for find. I personally find it one of the best references for this type of problem! ;-) Henry Stock wrote in message ... I want to use the following in a Do While Loop, but I don't know how to test when the value I am searching for is not found. One additional piece of information. When I do hit on a cell that has this value I modify the cell so that it no longer has the value I am Do While (??????) Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate ... Test to see if value is found.... Note... If value is not found I know I can't activate a cell, but I don't know where the active cell will end up ..... Other Processing .... Loop -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you test to break out of a find loop?
Hi, Henry. In the VBE Help, the article on the Find Method explains the
method returns Nothing if the text is not found. The linked example shows how to use it. I've copied the example he 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 WithHTHEd"Henry Stock" wrote in message ... I do not want to be impolite either, but yes... I did spend quite a bit of time trying to find the answer in help, both local and online. I wasted probably at least few hours looking to no avail, so that is why I am asking here. I could go into detail about variants I tried. I tried to test for a result from the find, in many languages functions return a value, boolean or numeric to indicate sucess or failure. That did not seem to work. I tried testing cell addresses before and after to see if the ActiveCell remained the same or went back to an original location. That did not work. I did searches on patterns like "Cells.Find", "Find", and others. I browsed for functions... I looked for info on the Cells object to see if I could find data on the "Find" method.... I must be a poor searcher if it that easy to find. "Loomah" wrote in message ... Henry I'm not trying to fob you off but have a look at the help file for find. I personally find it one of the best references for this type of problem! ;-) Henry Stock wrote in message ... I want to use the following in a Do While Loop, but I don't know how to test when the value I am searching for is not found. One additional piece of information. When I do hit on a cell that has this value I modify the cell so that it no longer has the value I am Do While (??????) Cells.Find(What:=":", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate ... Test to see if value is found.... Note... If value is not found I know I can't activate a cell, but I don't know where the active cell will end up ..... Other Processing .... Loop -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop or Find code ?? need help !! | Excel Worksheet Functions | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Break out of a loop in Excel2007? | Excel Discussion (Misc queries) | |||
VBA Loop to Find then Copy & Paste | Excel Discussion (Misc queries) | |||
Find & loop in VBA | Excel Discussion (Misc queries) |