Subscript out of range

[bw]

Sub DELETEWORKSHEETS()
Can someone explain what's wrong with the following? When I = 2 or 3, it
works ok, but when I = 4, I get a "Subscript out of range error. There are
no Sheets CalculateHere (2), CalculateHere (3), etc...
Thanks,
Bernie

Dim wSheet As Worksheet
Dim I As Integer
On Error Resume Next
I = 2
Do While I < 20
Set wSheet = Sheets("CalculateHere (" & I & ")") 'Error
Here..."Subscript out of range when I =4
If wSheet Is Nothing Then 'Doesn't exist
Set wSheet = Nothing
On Error GoTo ErrorOut
Else 'Does exist
wSheet.Delete
Set wSheet = Nothing
On Error GoTo 0
End If
ErrorOut:
I = I + 1
Loop
End Sub

 

[Earl Kiosterud]

Bernie,

The first loop (I=2), resume Next is in control, so when Sheets("Calculate
Now"... generates an error, it goes on. In the next iteration, the error
takes you to ErrorOut, but there's no Resume statement, so it's still in
error-handling in the next iteration (I=4) when you generate another error.
Perhaps you meant to put ErrorOut outside the loop with a Resume statement.

 

[Dave Peterson]

If you're going to ignore the error when you check to see if the worksheet
exists, why not just ignore the error when you delete the sheet:

Dim i As Long
i = 2
On Error Resume Next
Application.DisplayAlerts = False
Do While i < 20
Sheets("CalculateHere (" & i & ")").Delete
i = i + 1
Loop
Application.DisplayAlerts = true
On Error GoTo 0


But if you really wanted to check, you could check in line and then decide what
to do:

Dim wSheet As Worksheet
Dim I As Long
On Error Resume Next
Application.DisplayAlerts = False
I = 2
Do While I < 20
Set wSheet = Nothing
On Error Resume Next
Set wSheet = Sheets("CalculateHere (" & I & ")")
On Error GoTo 0
If wSheet Is Nothing Then
'do nothing
Else
wSheet.Delete
End If
I = I + 1
Loop
Application.DisplayAlerts = True

 

[Ben McBen]

Anyway - wouldnt you bet better off "for each"-ing through
your worksheets - or are there other sheets in your
worksheet?

ttfn benm

 

[bw]

Thanks for your help guys!
I decided to do it a different way. It works as I had intended.
Bernie

Sub DELETEWORKSHEETS()
Dim wSheet As Worksheet
Dim ws
On Error Resume Next
For Each wSheet In Worksheets
ws = wSheet.Name
If ws <> "CalculateHere" Then
If wSheet Is Nothing Then 'Doesn't exist
Set wSheet = Nothing
On Error GoTo 0
Else 'Does exist
' MsgBox "About to delete " & Chr$(34) & ws & Chr$(34)
wSheet.Delete
Set wSheet = Nothing
On Error GoTo 0
End If
End If
Next wSheet
End Sub