| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error runtime 9: deleting a worksheet
Hello,
I wrote following code and sometimes I get an runtime error 9. I don't really know when and why this error occurs. The value of test is never empty. On Error GoTo Marke Application.DisplayAlerts = False test = Worksheets("Länderansicht").Range("A" & i).Value ''''''''FEHLER'''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets(test).Select ''''''''''HIER TRITT DER FEHLER AUF '''''''FEHLER''''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets("Länderansicht").Select Worksheets("Länderansicht").Activate Worksheets(test).Delete Marke: Application.DisplayAlerts = True Tanks a lot, Michael |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error runtime 9: deleting a worksheet
You would get this error if test does not match the name of a sheet in the
activeworkbook. (could be a spelling error or a matter of extra spaces or example) -- Regards, Tom Ogilvy wrote in message om... Hello, I wrote following code and sometimes I get an runtime error 9. I don't really know when and why this error occurs. The value of test is never empty. On Error GoTo Marke Application.DisplayAlerts = False test = Worksheets("Länderansicht").Range("A" & i).Value ''''''''FEHLER'''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets(test).Select ''''''''''HIER TRITT DER FEHLER AUF '''''''FEHLER''''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets("Länderansicht").Select Worksheets("Länderansicht").Activate Worksheets(test).Delete Marke: Application.DisplayAlerts = True Tanks a lot, Michael |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error runtime 9: deleting a worksheet
I don't really understand why I get an error when I try to delete a
worksheet that does not exist. That's the reason I put the "on error" statement around the code. Is it possible to test whether the worksheet "test" exists or not? Thanks, Michael "Tom Ogilvy" wrote in message ... You would get this error if test does not match the name of a sheet in the activeworkbook. (could be a spelling error or a matter of extra spaces or example) -- Regards, Tom Ogilvy wrote in message om... Hello, I wrote following code and sometimes I get an runtime error 9. I don't really know when and why this error occurs. The value of test is never empty. On Error GoTo Marke Application.DisplayAlerts = False test = Worksheets("Länderansicht").Range("A" & i).Value ''''''''FEHLER'''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets(test).Select ''''''''''HIER TRITT DER FEHLER AUF '''''''FEHLER''''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets("Länderansicht").Select Worksheets("Länderansicht").Activate Worksheets(test).Delete Marke: Application.DisplayAlerts = True Tanks a lot, Michael |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error runtime 9: deleting a worksheet
Normally you wouldn't since you have an error handler. I suspect this is
in a loop and you never get out of error handling mode after you encounter the first sheet that doesn't exist - so you get the error the second time you encounter a sheet that does not exist -- an error in error handling mode halts execution. Try this: On Error GoTo Marke Application.DisplayAlerts = False test = Worksheets("Länderansicht").Range("A" & i).Value ''''''''FEHLER'''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets(test).Select ''''''''''HIER TRITT DER FEHLER AUF '''''''FEHLER''''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets("Länderansicht").Select Worksheets("Länderansicht").Activate Worksheets(test).Delete Marke: Resume NextMarke NextMarke: Application.DisplayAlerts = True -- Regards, Tom Ogilvy wrote in message m... I don't really understand why I get an error when I try to delete a worksheet that does not exist. That's the reason I put the "on error" statement around the code. Is it possible to test whether the worksheet "test" exists or not? Thanks, Michael "Tom Ogilvy" wrote in message ... You would get this error if test does not match the name of a sheet in the activeworkbook. (could be a spelling error or a matter of extra spaces or example) -- Regards, Tom Ogilvy wrote in message om... Hello, I wrote following code and sometimes I get an runtime error 9. I don't really know when and why this error occurs. The value of test is never empty. On Error GoTo Marke Application.DisplayAlerts = False test = Worksheets("Länderansicht").Range("A" & i).Value ''''''''FEHLER'''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets(test).Select ''''''''''HIER TRITT DER FEHLER AUF '''''''FEHLER''''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets("Länderansicht").Select Worksheets("Länderansicht").Activate Worksheets(test).Delete Marke: Application.DisplayAlerts = True Tanks a lot, Michael |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error runtime 9: deleting a worksheet
That's right! I always get the error the second time a worksheet doesn't
exist. Is there any possibility to get out of this error mode? Thanks, Michael "Tom Ogilvy" wrote in message ... Normally you wouldn't since you have an error handler. I suspect this is in a loop and you never get out of error handling mode after you encounter the first sheet that doesn't exist - so you get the error the second time you encounter a sheet that does not exist -- an error in error handling mode halts execution. Try this: On Error GoTo Marke Application.DisplayAlerts = False test = Worksheets("Länderansicht").Range("A" & i).Value ''''''''FEHLER'''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets(test).Select ''''''''''HIER TRITT DER FEHLER AUF '''''''FEHLER''''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets("Länderansicht").Select Worksheets("Länderansicht").Activate Worksheets(test).Delete Marke: Resume NextMarke NextMarke: Application.DisplayAlerts = True -- Regards, Tom Ogilvy wrote in message m... I don't really understand why I get an error when I try to delete a worksheet that does not exist. That's the reason I put the "on error" statement around the code. Is it possible to test whether the worksheet "test" exists or not? Thanks, Michael "Tom Ogilvy" wrote in message ... You would get this error if test does not match the name of a sheet in the activeworkbook. (could be a spelling error or a matter of extra spaces or example) -- Regards, Tom Ogilvy wrote in message om... Hello, I wrote following code and sometimes I get an runtime error 9. I don't really know when and why this error occurs. The value of test is never empty. On Error GoTo Marke Application.DisplayAlerts = False test = Worksheets("Länderansicht").Range("A" & i).Value ''''''''FEHLER'''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets(test).Select ''''''''''HIER TRITT DER FEHLER AUF '''''''FEHLER''''''''''''''''''''''''''''''''''''' '''''''''''''' Worksheets("Länderansicht").Select Worksheets("Länderansicht").Activate Worksheets(test).Delete Marke: Application.DisplayAlerts = True Tanks a lot, Michael |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro runtime 1004 error on opening worksheet | Excel Discussion (Misc queries) | |||
| Set worksheet range runtime error 1004 | Excel Programming | |||
| Validation.Modify Runtime Error in Protected Worksheet | Excel Programming | |||
| RUNTIME ERROR '1004' --- Select method of worksheet class failed | Excel Programming | |||
Linear Mode
