View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Jim Rech Jim Rech is offline
external usenet poster
 
Posts: 2,718
Default Cleaner Error trap in Loop.

OnError works differently than you would think. You cannot GoTo where you
want code to continue executing directly. You have to GoTo an error handler
which then Resumes where you want the code to continue. Here's the rule:
Every OnError must have a matching Resume. So:

Do While Not IsEmpty(ActiveCell)
If IsEmpty(ActiveCell) Then
Exit Do
Else
Vfind = ActiveCell.Value
Sheets("Combined").Select
On Error GoTo NoMatch ''<<CHANGED
Cells.Find(What:=Vfind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
FindAgain: Sheets("VoidReq").Select
ActiveCell.Offset(1).Select ''<<Junk deleted
End If
Loop
,,,,,, ''Whatever else you may have
Exit Sub <<ADDED -This goes at the end of the sub
NoMatch: <<ADDED
Resume FindAgain <ADDED
End Sub

PS I didn't check that the rest of your code runs okay,
--
Jim
"plys" wrote in message
...
In the following code, I'm tying to go down the "Combined" worksheet's
column's cells locating the values of the cell in the worksheet "VoidReq"
&
delete that sheets row if found, continuing down the "Combined" worksheet's
column's cell until it runs into empty cell.

The problem is sometimes it will not find the value on the "Combined"
sheet
& I want it to skip over deleting a row & continue on down the column
until
it gets to the empty cell at the bottom. I have an error catch "Error
GoTo
FindAgain" but in running the code it seems to only skip over one not
found
entry & when it runs into another not found entry it craps out.

I know the code is I have is probably sloppy & I apologize but I'm still a
struggling learner.

Do While Not IsEmpty(ActiveCell)
If IsEmpty(ActiveCell) Then
Exit Do
Else
Vfind = ActiveCell.Value
Sheets("Combined").Select
On Error GoTo FindAgain
Cells.Find(What:=Vfind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
FindAgain: Sheets("VoidReq").Select
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Loop