error runtime 9: deleting a worksheet

[[email protected]]

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

[Tom Ogilvy]

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


<[email protected]> wrote in message
news:[email protected]...
> 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

[[email protected]]

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" <[email protected]> wrote in message news:<[email protected]>...
> 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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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

[Tom Ogilvy]

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


<[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:<[email protected]>...
> > 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
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

[[email protected]]

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" <[email protected]> wrote in message news:<[email protected]>...
> 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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:<[email protected]>...
> > > 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
> > >
> > >
> > > <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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