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

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

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


.

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


.

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


.