On Error Resume Next problem

[Guest]

In my code after my DIM statements I have:

On Error GoTo wsAdd
With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With

Let's say MySummary (sheet) does not exist - so macro jumps to:

wsAdd:
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "MySummary"
On Error GoTo 0
Resume Next '<< Creates and Names Sheet MySummary

an returns to the line:

.Activate << Where the Macro Bombs -- What is wrong??

 

[Don Guillett]

try
On Error GoTo wsadd
With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With
wsadd:
Sheets.Add
ActiveSheet.Name = "MySummary"

 

[Myrna Larson]

As the error message says, "... With block variable not set". A statement like

With Sheets("MySummary")

creates an internal (hidden) object variable, and the statements within the
With block operate on that object.

In your case, the object doesn't exist, so no object variable was created, and
your on error code doesn't do anything to change that. You need to re-execute
the With statement so that variable is created. You do that with Resume
instead of Resume Next.

Also, you've put the On Error Goto 0 statement inside the trap. That means
that if the sheet DOES exist, error trapping will never be turned off, and
that's not good. Your trap only applies to this particular statement, and you
don't want it to execute in case of some other error, so it must be turned off
in all cases. IOW, the On Error Goto 0 statements belongs in the main code,
not in the trap.

Also, you don't need to activate the sheet to clear it.

The code should look like this:

On Error GoTo wsAdd
Sheets("MySummary").Cells.ClearContents
On Error GoTo 0
'
'
'
Exit Sub

wsAdd:
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "MySummary"
Resume 'i.e. execute original statement again


Or you can put the whole thing in-line, like this:

Dim i As Long

On Error Resume Next
i = Sheets("MySummary").Index

If Err.Number <> 0 Then
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "MySummary"
End If
On Error GoTo 0

With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With

'supposedly more stuff here

 

[Guest]

Don thanks, but I have about 30 lines of code between the:

End With

and the

wsadd:

and I was hoping to take advantage of the Resume Next line

Your suggested code look almost the same as what I have already...
hummmmmm.. (in doubt..)
Tks,

Jim

 

[Guest]

Give this a whirl. It avoids jumping around by using a worksheet object...

dim wksSummary as worksheet

on error resume next
set wksSummary = Sheets("Summary")
on error goto 0

if wksSummary is nothing then
sheets.add
activesheet.name = "Summary"
end if
'continue your code here...

 

[Myrna Larson]

Hi, Don:

A couple of comments:

You need an Exit Sub statement above the wsadd: line, so that, when the sheet
DOES exist, you won't add another sheet and give it the same name.

IMO, it's not good practice to "fall into" error trapping code when no error
has occurred. That can cause other errors. For example, if the trap included a
Resume statement, that would cause another error, Resume Without Error, IIRC.

But you don't have a Resume statement. If the trap code is at the bottom of
the sub (as is usually the case), the sub will exit after adding the
worksheet. I guess that's OK if the sub ONLY clears the contents of this
sheet. If it does something else, like entering some headers, formatting, etc,
you need to Resume, and with a RESUME statement rather than RESUME NEXT.

If there is more work to be done, to eliminate the redundancy of clearing a
known-empty worksheet, you could Resume at a statement after the ClearContents
line.

 

[Guest]

Slick Jim - I will give it a go...
Thanks,
Jim

Give this a whirl. It avoids jumping around by using a worksheet object...

dim wksSummary as worksheet

on error resume next
set wksSummary = Sheets("Summary")
on error goto 0

if wksSummary is nothing then
sheets.add
activesheet.name = "Summary"
end if
'continue your code here...

 

[Guest]

Myrna,

I've never received a more comprehensive answer -- thanks so much - I've
printed out you explanation and can only hope that **after reading and
studying ** I can fully understand (as you do). I've used your suggested
Final copy and it works like I wanted it to in the first place.

Again, many thanks...
Jim May

 

[Myrna Larson]

I hope you have taken note of my comments re the Resume vs Resume Next issue,
and the placement of the On Error Goto 0 statements.

Particularly the latter. You don't want to execute this particular trap for
some error in your code later down.

 

[Myrna Larson]

You can ignore my last response where I said I hoped you had read my previous
post. Looking at the times of it and your response to Jim, I thought maybe you
had missed it.

Glad you have it working now.

 

[Dave Peterson]

One more option...

If you don't have anything against adding the sheet, maybe it's ok to just
delete it and start from scratch:

on error resume next
worksheets("mySummary").delete

 

[Guest]

Thanks Dave, I understand your code..

One more option...

If you don't have anything against adding the sheet, maybe it's ok to just
delete it and start from scratch:

on error resume next
worksheets("mySummary").delete

 

[Guest]

Thanks Myrna, I read through your explanation and I actually understand what
you are saying. You are a PRO !!
Jim

 

[Guest]

although he'll probably want to suppress the "are you sure" message

application.displayalerts = false
on error resume next
worksheets("mySummary").delete

 

[Don Guillett]

Thanks for the catch

Exit Sub statement