Existance Check Fails
 

I check for the existance of a worksheet in my workbook...if it exists
I want to delete it. I run through a list of sheet names and delete
them if they exist. This works.... e.g. getting a positive response
that the sheet exists. But I am also getting a positive response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name = sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n

Existance Check Fails
 

Dim SheetExists As Object

For n = 6 To 1
Set SheetExists = Nothing
On Error Resume Next
Set SheetExists = Sheets(sheetname(n))
On Error GoTo 0
If Not SheetExists is Nothing Then
' delete it if it exists"
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
End If
Next n

--
Regards,
Tom Ogilvy

"ChuckM" wrote in message
m...
I check for the existance of a worksheet in my workbook...if it exists
I want to delete it. I run through a list of sheet names and delete
them if they exist. This works.... e.g. getting a positive response
that the sheet exists. But I am also getting a positive response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name = sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n

Existance Check Fails
 

-----Original Message-----
I check for the existance of a worksheet in my
workbook...if it exists
I want to delete it. I run through a list of sheet
names and delete
them if they exist. This works.... e.g. getting a
positive response
that the sheet exists. But I am also getting a positive
response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name =
sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n
.


Follow your code: let's say sheetname(1) refers to a sheet
that exists, sheetname(2) is not there...

First time through, it finds sheetname(1), sets
SheetExists to True, and deletes the sheet with the
corresponding name.

Second time through: it reaches the line "SheetExists
= ..." and errors out, since there is no Sheets(sheetname
(n)). It resumes at the next line, but SheetExists is
STILL equal to True!!! So that is why it then tries to
delete the sheet that does not exist.

A better way to do this: Instead of stepping though your
names, step through each worksheet and see if the name is
in your list:

Dim DeleteIt as Boolean, n as Integer, n1 as Integer
Dim sheetname(6) as String

' Define your sheetnames here and store in sheetname()

For n = 1 To Sheets.Count
DeleteIt = False
For n1 = 1 To 6
If Sheets(n).Name = sheetname(n1) Then DeleteIt =
True
Next n1
If DeleteIt Then
'put code here to delete Sheets(n)
End If
Next n

Existance Check Fails
 

Looping 6 x sheets.count is better than failing, but not necessarily a good
way.

See my post for a way to only loop once through the list of sheets to
delete.

--
Regards,
Tom Ogilvy

"K Dales" wrote in message
...

-----Original Message-----
I check for the existance of a worksheet in my
workbook...if it exists
I want to delete it. I run through a list of sheet
names and delete
them if they exist. This works.... e.g. getting a
positive response
that the sheet exists. But I am also getting a positive
response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name =
sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n
.


Follow your code: let's say sheetname(1) refers to a sheet
that exists, sheetname(2) is not there...

First time through, it finds sheetname(1), sets
SheetExists to True, and deletes the sheet with the
corresponding name.

Second time through: it reaches the line "SheetExists
= ..." and errors out, since there is no Sheets(sheetname
(n)). It resumes at the next line, but SheetExists is
STILL equal to True!!! So that is why it then tries to
delete the sheet that does not exist.

A better way to do this: Instead of stepping though your
names, step through each worksheet and see if the name is
in your list:

Dim DeleteIt as Boolean, n as Integer, n1 as Integer
Dim sheetname(6) as String

' Define your sheetnames here and store in sheetname()

For n = 1 To Sheets.Count
DeleteIt = False
For n1 = 1 To 6
If Sheets(n).Name = sheetname(n1) Then DeleteIt =
True
Next n1
If DeleteIt Then
'put code here to delete Sheets(n)
End If
Next n

Existance Check Fails
 

Agreed, your code is more efficient, but I do hate to turn
off error checking - there can be other reasons the code
could fail. I guess it depends on how much speed and
efficiency matter - for most practical applications I
doubt the user would notice the difference in speed.

-----Original Message-----

Looping 6 x sheets.count is better than failing, but not
necessarily a good
way.

See my post for a way to only loop once through the list
of sheets to
delete.

--
Regards,
Tom Ogilvy

"K Dales" wrote in
message
...

-----Original Message-----
I check for the existance of a worksheet in my
workbook...if it exists
I want to delete it. I run through a list of sheet
names and delete
them if they exist. This works.... e.g. getting a
positive response
that the sheet exists. But I am also getting a positive
response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name =
sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n
.


Follow your code: let's say sheetname(1) refers to a
sheet
that exists, sheetname(2) is not there...

First time through, it finds sheetname(1), sets
SheetExists to True, and deletes the sheet with the
corresponding name.

Second time through: it reaches the line "SheetExists
= ..." and errors out, since there is no Sheets
(sheetname
(n)). It resumes at the next line, but SheetExists is
STILL equal to True!!! So that is why it then tries to
delete the sheet that does not exist.

A better way to do this: Instead of stepping though your
names, step through each worksheet and see if the name
is
in your list:

Dim DeleteIt as Boolean, n as Integer, n1 as Integer
Dim sheetname(6) as String

' Define your sheetnames here and store in sheetname()

For n = 1 To Sheets.Count
DeleteIt = False
For n1 = 1 To 6
If Sheets(n).Name = sheetname(n1) Then DeleteIt =
True
Next n1
If DeleteIt Then
'put code here to delete Sheets(n)
End If
Next n


.

Existance Check Fails
 

I understand your point, but . . .

Be hard to squeeze in any unexpected error as written:

On Error Resume Next
Set SheetExists = Sheets(sheetname(n))
On Error GoTo 0

That is a capability offered by the language; in fact I would say it is
inherent in the design. You could as easily say, I don't like to Loop or I
don't like IF statements. Just because it has the name Error in it doesn't
mean it should be avoided when it is appropriate.


--
Regards,
Tom Ogilvy


"K Dales" wrote in message
...
Agreed, your code is more efficient, but I do hate to turn
off error checking - there can be other reasons the code
could fail. I guess it depends on how much speed and
efficiency matter - for most practical applications I
doubt the user would notice the difference in speed.

-----Original Message-----

Looping 6 x sheets.count is better than failing, but not
necessarily a good
way.

See my post for a way to only loop once through the list
of sheets to
delete.

--
Regards,
Tom Ogilvy

"K Dales" wrote in
message
...

-----Original Message-----
I check for the existance of a worksheet in my
workbook...if it exists
I want to delete it. I run through a list of sheet
names and delete
them if they exist. This works.... e.g. getting a
positive response
that the sheet exists. But I am also getting a positive
response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name =
sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n
.


Follow your code: let's say sheetname(1) refers to a
sheet
that exists, sheetname(2) is not there...

First time through, it finds sheetname(1), sets
SheetExists to True, and deletes the sheet with the
corresponding name.

Second time through: it reaches the line "SheetExists
= ..." and errors out, since there is no Sheets
(sheetname
(n)). It resumes at the next line, but SheetExists is
STILL equal to True!!! So that is why it then tries to
delete the sheet that does not exist.

A better way to do this: Instead of stepping though your
names, step through each worksheet and see if the name
is
in your list:

Dim DeleteIt as Boolean, n as Integer, n1 as Integer
Dim sheetname(6) as String

' Define your sheetnames here and store in sheetname()

For n = 1 To Sheets.Count
DeleteIt = False
For n1 = 1 To 6
If Sheets(n).Name = sheetname(n1) Then DeleteIt =
True
Next n1
If DeleteIt Then
'put code here to delete Sheets(n)
End If
Next n


.

Existance Check Fails
 

Guess I am just too "old school" (hmmm, won't reveal my
age, but the first "PC" I worked on was a Sol-20!)

-----Original Message-----
I understand your point, but . . .

Be hard to squeeze in any unexpected error as written:

On Error Resume Next
Set SheetExists = Sheets(sheetname(n))
On Error GoTo 0

That is a capability offered by the language; in fact I
would say it is
inherent in the design. You could as easily say, I don't
like to Loop or I
don't like IF statements. Just because it has the name
Error in it doesn't
mean it should be avoided when it is appropriate.


--
Regards,
Tom Ogilvy


"K Dales" wrote in
message
...
Agreed, your code is more efficient, but I do hate to
turn
off error checking - there can be other reasons the code
could fail. I guess it depends on how much speed and
efficiency matter - for most practical applications I
doubt the user would notice the difference in speed.

-----Original Message-----

Looping 6 x sheets.count is better than failing, but
not
necessarily a good
way.

See my post for a way to only loop once through the
list
of sheets to
delete.

--
Regards,
Tom Ogilvy

"K Dales" wrote
in
message
...

-----Original Message-----
I check for the existance of a worksheet in my
workbook...if it exists
I want to delete it. I run through a list of sheet
names and delete
them if they exist. This works.... e.g. getting a
positive response
that the sheet exists. But I am also getting a
positive
response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name =
sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n
.


Follow your code: let's say sheetname(1) refers to a
sheet
that exists, sheetname(2) is not there...

First time through, it finds sheetname(1), sets
SheetExists to True, and deletes the sheet with the
corresponding name.

Second time through: it reaches the line "SheetExists
= ..." and errors out, since there is no Sheets
(sheetname
(n)). It resumes at the next line, but SheetExists
is
STILL equal to True!!! So that is why it then tries
to
delete the sheet that does not exist.

A better way to do this: Instead of stepping though
your
names, step through each worksheet and see if the
name
is
in your list:

Dim DeleteIt as Boolean, n as Integer, n1 as Integer
Dim sheetname(6) as String

' Define your sheetnames here and store in sheetname
()

For n = 1 To Sheets.Count
DeleteIt = False
For n1 = 1 To 6
If Sheets(n).Name = sheetname(n1) Then
DeleteIt =
True
Next n1
If DeleteIt Then
'put code here to delete Sheets(n)
End If
Next n


.



.

Existance Check Fails
 

Timex Sinclair besides mainframes.

--
Regards,
Tom Ogilvy

K Dales wrote in message
...
Guess I am just too "old school" (hmmm, won't reveal my
age, but the first "PC" I worked on was a Sol-20!)

-----Original Message-----
I understand your point, but . . .

Be hard to squeeze in any unexpected error as written:

On Error Resume Next
Set SheetExists = Sheets(sheetname(n))
On Error GoTo 0

That is a capability offered by the language; in fact I
would say it is
inherent in the design. You could as easily say, I don't
like to Loop or I
don't like IF statements. Just because it has the name
Error in it doesn't
mean it should be avoided when it is appropriate.


--
Regards,
Tom Ogilvy


"K Dales" wrote in
message
...
Agreed, your code is more efficient, but I do hate to
turn
off error checking - there can be other reasons the code
could fail. I guess it depends on how much speed and
efficiency matter - for most practical applications I
doubt the user would notice the difference in speed.

-----Original Message-----

Looping 6 x sheets.count is better than failing, but
not
necessarily a good
way.

See my post for a way to only loop once through the
list
of sheets to
delete.

--
Regards,
Tom Ogilvy

"K Dales" wrote
in
message
...

-----Original Message-----
I check for the existance of a worksheet in my
workbook...if it exists
I want to delete it. I run through a list of sheet
names and delete
them if they exist. This works.... e.g. getting a
positive response
that the sheet exists. But I am also getting a
positive
response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name =
sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n
.


Follow your code: let's say sheetname(1) refers to a
sheet
that exists, sheetname(2) is not there...

First time through, it finds sheetname(1), sets
SheetExists to True, and deletes the sheet with the
corresponding name.

Second time through: it reaches the line "SheetExists
= ..." and errors out, since there is no Sheets
(sheetname
(n)). It resumes at the next line, but SheetExists
is
STILL equal to True!!! So that is why it then tries
to
delete the sheet that does not exist.

A better way to do this: Instead of stepping though
your
names, step through each worksheet and see if the
name
is
in your list:

Dim DeleteIt as Boolean, n as Integer, n1 as Integer
Dim sheetname(6) as String

' Define your sheetnames here and store in sheetname
()

For n = 1 To Sheets.Count
DeleteIt = False
For n1 = 1 To 6
If Sheets(n).Name = sheetname(n1) Then
DeleteIt =
True
Next n1
If DeleteIt Then
'put code here to delete Sheets(n)
End If
Next n


.



.