Loop Error Trap

M

mburkett

I am getting an error in the code below after the first loop though IF
there is an error in finding the value in the worksheet. The first
loop though works correctly. Here is the error: Run-time error '91'
Object variable or With block variable not set

X = 0
Do Until X = pdatecnt2

Range("a1").Select
ActiveCell.Offset(X, 0).Range("A1").Select
accttodel = Selection.Value
aSheet.Activate
Range("a1").Select
Range("A:A").Select
On Error GoTo nextx
'The error occurs in the following line
Selection.Find(What:=accttodel, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
nextx:
Sheets("temp").Select

X = X + 1
Loop


Thank you for taking a look -Michael
 
A

AndyM

You will receive a run-time error '91' on the Selection.Find line of code if
it cannot find the value specified.

In this case, you are looking for accttodel after the active cell. You can
catch this error by adding error handling.

At the top of your code add "On Error GoTo errHandler"

Then at the end of your module add:
errHandler:
If err.Num = 91 Then
MsgBox "Unable to find value:" & accttodel
End
End If
 
M

mburkett

You will receive a run-time error '91' on the Selection.Find line of codeif
it cannot find the value specified.

In this case, you are looking for accttodel after the active cell.  Youcan
catch this error by adding error handling.

At the top of your code add "On Error GoTo errHandler"

Then at the end of your module add:
errHandler:
      If err.Num = 91 Then
            MsgBox "Unable to find value:" & accttodel
            End
      End If










- Show quoted text -

Thanks for the quick reply. I am actually looking for a series of
values. I need the loop to continue even if it doesn't find a value. I
have an error trap in the original code and it works for the first
error (ie the first value that is not found with the find statement)
but subsequent errors, I get the run-time error specified. Ideas?
 
A

AndyM

Ok, keep your nextx: line in the code, but also add an error handler at the
bottom of the procedure.

Something like:

Exit Sub
errHandler:
err.Clear
Resume nextx
End Sub

Then at the top of the procedure add "On Error GoTo errHandler". Take out
the "On Error GoTo nextx" line.

This will allow multiple errors to go through the error handler. The Exit
Sub is needed once the code finishes it does not try to execute the code
below the errHandler: line.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top