Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Error Trap
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Error Trap
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 "mburkett" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Error Trap
On Sep 26, 11:34*pm, AndyM wrote:
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 "mburkett" wrote: 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- Hide quoted text - - 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Error Trap
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. "mburkett" wrote: On Sep 26, 11:34 pm, AndyM wrote: 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 "mburkett" wrote: 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- Hide quoted text - - 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set Error handling INSIDE error-trap | Excel Programming | |||
How to trap an error with onError in a for i loop | Excel Programming | |||
Cleaner Error trap in Loop. | Excel Discussion (Misc queries) | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming |