Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro help.
I have a MAcro that uses a the find ~Find function~. My
problem is when the macro can not find the word any further it crashes instead of exiting the loop. I assume (And we all know what that stands for)it is because of the Microsoft error message of no matches were found. How can I bypass this to exit the loop and continue on? Any help is well recieved. Pete W |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro help.
Hope this helps
Tom Sub Find() Dim r As Range Dim strAddress As String Set r = ActiveSheet.Cells.Find("Hello") If Not r Is Nothing Then strAddress = r.Address Do MsgBox r.Address Set r = Cells.FindNext(r) Loop While Not r Is Nothing And r.Address < strAddress End If End Sub "Pete" schrieb im Newsbeitrag ... I have a MAcro that uses a the find ~Find function~. My problem is when the macro can not find the word any further it crashes instead of exiting the loop. I assume (And we all know what that stands for)it is because of the Microsoft error message of no matches were found. How can I bypass this to exit the loop and continue on? Any help is well recieved. Pete W |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro help.
Tom
I can't seem to get your code to work the way I want it to. Here is my code as it sits now. The macro will run about 10 times before crashing. The error states ~Run-Time Error 91 - Object Varible or With Block Varible not set.~ Sub Find() Page = "Page" Do Cells.Find(What:="Page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Rows("1:11").EntireRow.Select Selection.Delete Shift:=xlUp ActiveCell.Offset(-6, 0).Range("A1").Select Loop Until Page = 0 End Sub Any suggestions Pete W -----Original Message----- Hope this helps Tom Sub Find() Dim r As Range Dim strAddress As String Set r = ActiveSheet.Cells.Find("Hello") If Not r Is Nothing Then strAddress = r.Address Do MsgBox r.Address Set r = Cells.FindNext(r) Loop While Not r Is Nothing And r.Address < strAddress End If End Sub "Pete" schrieb im Newsbeitrag ... I have a MAcro that uses a the find ~Find function~. My problem is when the macro can not find the word any further it crashes instead of exiting the loop. I assume (And we all know what that stands for)it is because of the Microsoft error message of no matches were found. How can I bypass this to exit the loop and continue on? Any help is well recieved. Pete W . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro help.
Your code crashes when you have deleted all the instances of Page and your
trying to activate nothing. By the way, why have page = "Page" and then search for the string literal "Page" instead of the variable value of page? Anyway, I am sure Tom will have a solution for you. -- Regards, Tom Ogilvy "Pete" wrote in message ... Tom I can't seem to get your code to work the way I want it to. Here is my code as it sits now. The macro will run about 10 times before crashing. The error states ~Run-Time Error 91 - Object Varible or With Block Varible not set.~ Sub Find() Page = "Page" Do Cells.Find(What:="Page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Rows("1:11").EntireRow.Select Selection.Delete Shift:=xlUp ActiveCell.Offset(-6, 0).Range("A1").Select Loop Until Page = 0 End Sub Any suggestions Pete W -----Original Message----- Hope this helps Tom Sub Find() Dim r As Range Dim strAddress As String Set r = ActiveSheet.Cells.Find("Hello") If Not r Is Nothing Then strAddress = r.Address Do MsgBox r.Address Set r = Cells.FindNext(r) Loop While Not r Is Nothing And r.Address < strAddress End If End Sub "Pete" schrieb im Newsbeitrag ... I have a MAcro that uses a the find ~Find function~. My problem is when the macro can not find the word any further it crashes instead of exiting the loop. I assume (And we all know what that stands for)it is because of the Microsoft error message of no matches were found. How can I bypass this to exit the loop and continue on? Any help is well recieved. Pete W . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro help.
At the risk of duplication:
Sub FindPage() Dim rng As Range Page = "Page" Set rng = Cells.Find(What:=Page, After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Do rng.Rows("1:11").EntireRow.Delete Shift:=xlUp Set rng = Cells.FindNext(Range("A1")) Loop While Not rng Is Nothing End If End Sub -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Your code crashes when you have deleted all the instances of Page and your trying to activate nothing. By the way, why have page = "Page" and then search for the string literal "Page" instead of the variable value of page? Anyway, I am sure Tom will have a solution for you. -- Regards, Tom Ogilvy "Pete" wrote in message ... Tom I can't seem to get your code to work the way I want it to. Here is my code as it sits now. The macro will run about 10 times before crashing. The error states ~Run-Time Error 91 - Object Varible or With Block Varible not set.~ Sub Find() Page = "Page" Do Cells.Find(What:="Page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Rows("1:11").EntireRow.Select Selection.Delete Shift:=xlUp ActiveCell.Offset(-6, 0).Range("A1").Select Loop Until Page = 0 End Sub Any suggestions Pete W -----Original Message----- Hope this helps Tom Sub Find() Dim r As Range Dim strAddress As String Set r = ActiveSheet.Cells.Find("Hello") If Not r Is Nothing Then strAddress = r.Address Do MsgBox r.Address Set r = Cells.FindNext(r) Loop While Not r Is Nothing And r.Address < strAddress End If End Sub "Pete" schrieb im Newsbeitrag ... I have a MAcro that uses a the find ~Find function~. My problem is when the macro can not find the word any further it crashes instead of exiting the loop. I assume (And we all know what that stands for)it is because of the Microsoft error message of no matches were found. How can I bypass this to exit the loop and continue on? Any help is well recieved. Pete W . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Loop | Excel Discussion (Misc queries) | |||
loop a macro | Excel Discussion (Misc queries) | |||
how can i loop a macro | Excel Discussion (Misc queries) | |||
macro loop | Excel Discussion (Misc queries) | |||
Loop macro help. | Excel Programming |