Error check and resume

[Steve]

After reading:

http://www.microsoft.com/office/com...d9-8feae6d75298&catlist=&dglist=&ptlist=&exp=

I have ajusted the code below to add some ErrCheck statements below, and I get the following error now:

Compile Error
Lable not defined

and the statement "On Error GoTo ErrCheck4" is highlighted.

Thanks for your help.



Sub DeleteEmptySteve5()

Dim sht As Worksheet

Rem Collect all the worksheets together.
For Each sht In ActiveWorkbook.Worksheets
sht.Select False
Next

Rem The workbook in now in "Group" mode.
Rem The settings below will apply to all those selected sheets.

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

Dim test1 As Boolean
Dim test2 As Boolean
Dim test3 As Boolean
Dim test4 As Boolean
Dim test5 As Boolean

With ActiveSheet.UsedRange
.Value = .Value
End With

ActiveSheet.Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
ActiveCell.Select

Check1:
On Error GoTo ErrCheck1
ActiveWindow.FreezePanes = False
test1 = True

Check2:
On Error GoTo ErrCheck2
Rows.Hidden = False
Columns.Hidden = False
test2 = True

Check3:
On Error GoTo ErrCheck3
ActiveSheet.Cells.Rows.Ungroup
ActiveSheet.Cells.Rows.Ungroup
test3 = True

Check4:
On Error GoTo ErrCheck4
ActiveSheet.Shapes("Drop Down 1").Select
Selection.Cut
test4 = True

Check5:
For Each cel In Range("E1:E1000")
cel.Value = Application.WorksheetFunction.trim(cel.Value)
Next cel
test5 = True

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "C").Value = "Volume" Or _
.Cells(Lrow, "C").Value = "Gross-margin-target-$-per-gallon" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-per-gallon" Or _
.Cells(Lrow, "C").Value = "Gross-margin-target-$-total" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-total" Or _
.Cells(Lrow, "g").Value = "" Then .Rows(Lrow).Delete
'Or use this if you want to check more values.

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

ErrCheck1:
Resume Check2

ErrCheck2:
Resume Check3

ErrCheck3:
Resume Check4

ErrCheck4:
Resume Check5

 

[Tom Ogilvy]

You have an End Sub statement above your labels, so the labels are not
within the scope of your procedure. Move the End Sub to the actual bottom of
the subroutine.

 

[Steve]

Thanks very much for your response Tom. I wouldn't have figured that out since I erroneously thought the "end sub" belonged above the error checks.

Since my last post I've been working more with my macro. I bought a book Excel 2003 VBA Programmer's Reference and I've spent hours with it but it hasn't helped me develop this macro. I guess I'll have to get one of those dummy books.

Below is my updated attempt with my macro. I changed the error coding in a manner that I thought made more sense.

Now when I run the macro, I get a Run-Time Error 438 at
"With ActiveWorkbook.sht.UsedRange".

If I delete that selection, the code works until it gets to
"Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel"

But then it never seems to go beyond that and delete any rows. When I click F8 to see whats going on it seems to loop back and forth between those two lines and never continue on to the next part of the code.

And for all my efforts, I can't get any part of this macro to affect anything other than the active worksheet in the workbook. All other worksheets remain unaffected.

Tom, Nigel, all, thanks very much for your help.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
My Macro as of 8/1:

Sub DeleteEmptySteve100()


Dim sht As Worksheets
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveWorkbook.sht.UsedRange
.Value = .Value
End With

On Error Resume Next
ActiveWorkbook.sht
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0

On Error Resume Next
ActiveWindow.FreezePanes = False

On Error Resume Next
Rows.Hidden = False
Columns.Hidden = False

On Error Resume Next
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Columns.Ungroup

On Error Resume Next
ActiveWorkbook.sht.Shapes("Drop Down 1").Select
Selection.Cut

On Error Resume Next
For Each sht In ActiveWorkbook.sht
sht.Select False
Next
For Each Cel In Range("E1:E1000")
Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel

Firstrow = ActiveWorkbook.sht.UsedRange.Cells(1).Row
Lastrow = ActiveWorkbook.sht.UsedRange.Rows.Count + Firstrow - 1
With ActiveWorkbook.sht
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "C").Value = "Volume" Or _
.Cells(Lrow, "C").Value = "Gross-margin-target-$-per-gallon" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-per-gallon" Or _
.Cells(Lrow, "C").Value = "Gross-margin-target-$-total" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-total" Or _
.Cells(Lrow, "g").Value = "" Then .Rows(Lrow).Delete
'Or use this if you want to check more values.

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Chip Pearson]

Steve,

Change

With ActiveWorkbook.sht.UsedRange
to
With sht.UsedRange



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Thanks very much for your response Tom. I wouldn't have

figured that out since I erroneously thought the "end sub"
belonged above the error checks.

Since my last post I've been working more with my macro. I

bought a book Excel 2003 VBA Programmer's Reference and I've
spent hours with it but it hasn't helped me develop this macro.
I guess I'll have to get one of those dummy books.

Below is my updated attempt with my macro. I changed the error

coding in a manner that I thought made more sense.

Now when I run the macro, I get a Run-Time Error 438 at
"With ActiveWorkbook.sht.UsedRange".

If I delete that selection, the code works until it gets to
"Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel"

But then it never seems to go beyond that and delete any rows.

When I click F8 to see whats going on it seems to loop back and
forth between those two lines and never continue on to the next
part of the code.

And for all my efforts, I can't get any part of this macro to

affect anything other than the active worksheet in the workbook.
All other worksheets remain unaffected.

Tom, Nigel, all, thanks very much for your help.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
My Macro as of 8/1:

Sub DeleteEmptySteve100()


Dim sht As Worksheets
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveWorkbook.sht.UsedRange
.Value = .Value
End With

On Error Resume Next
ActiveWorkbook.sht
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0

On Error Resume Next
ActiveWindow.FreezePanes = False

On Error Resume Next
Rows.Hidden = False
Columns.Hidden = False

On Error Resume Next
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Columns.Ungroup

On Error Resume Next
ActiveWorkbook.sht.Shapes("Drop Down 1").Select
Selection.Cut

On Error Resume Next
For Each sht In ActiveWorkbook.sht
sht.Select False
Next
For Each Cel In Range("E1:E1000")
Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel

Firstrow = ActiveWorkbook.sht.UsedRange.Cells(1).Row
Lastrow = ActiveWorkbook.sht.UsedRange.Rows.Count + Firstrow - 1
With ActiveWorkbook.sht
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "C").Value = "Volume" Or _
.Cells(Lrow, "C").Value =

"Gross-margin-target-$-per-gallon" Or _

.Cells(Lrow, "C").Value =

"Economic-profit-target-$-per-gallon" Or _

.Cells(Lrow, "C").Value = "Gross-margin-target-$-total" Or _
.Cells(Lrow, "C").Value =

"Economic-profit-target-$-total" Or _

 

[Chip Pearson]

Steve,

You should Dim the sht variable as Worksheet, not Worksheets.
Then you need to Set it to some particular worksheet. Without
this Set initialization, the variable doesn't refer to any actual
worksheet and you'll get a run time error 91. E.g.,

Dim sht As Worksheet
Set sht = ActiveSheet


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Thanks very much Chip

I applied what you suggested throughout my code and it corrected the immediate issue.

However, still had trouble with getting the trim function to

work, so I added Option Explicit at top and added "Dim cell as
Range." That seems to let the code pass through the trim code,
but now I get a Run-time error 91 Object Variable or With block
variable not set at "with sht.UsedRange" highlighted in the
debugger. I've tried Dim UsedRange as Range, but that does not
work.

 

[Dave Peterson]

John Walkenbach's book get high reviews whenever someone asks.

For excel books, Debra Dalgleish has a big list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. I think that John Green
(and others) is nice, too (but maybe for your second book).

And I'm betting that you don't want to set sht to all the sheets in the
workbook.

Do you want to loop through all the sheets?

if yes:


dim sht as worksheet
'some other stuff you need

for each sht in activeworkbook.worksheets
'your code that does all the work
next sht

 

[SteveC]

Thanks Dave,
I'll return the book I got on saturday and trade it in for Walkenbachs.

Your advice lets my code progress a few more lines before it gets stuck again with a Run-time error '91': Object variable with Block variable not set, this time at:

"With sht.UsedRange" , just above ".Value = Value."

None of my attempts at setting "UsedRange" to anything helps.

My code as it now stands is below:



Option Explicit

Sub DeleteEmptySteve140()

Dim sht As Worksheet
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim cell As Range

Set sht = ActiveSheet

For Each sht In ActiveWorkbook.Worksheets
Next sht

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With sht.UsedRange
.Value = .Value
End With

On Error Resume Next
With sht.UsedRange
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
End With

On Error Resume Next
ActiveWindow.FreezePanes = False

On Error Resume Next
Rows.Hidden = False
Columns.Hidden = False

On Error Resume Next
sht.Rows.Ungroup
sht.Rows.Ungroup
sht.Columns.Ungroup

On Error Resume Next
sht.Shapes("Drop Down 1").Select
Selection.Cut

On Error Resume Next
With sht.Range("E1:E800").Select
For Each cell In Selection
cell.Value = Application.trim(cell.Value)
Next

Firstrow = sht.UsedRange.Cells(1).Row
Lastrow = sht.UsedRange.Rows.Count + Firstrow - 1
With sht
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "C").Value = "Volume" Or _
.Cells(Lrow, "C").Value = "Gross-margin-target-$-per-gallon" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-per-gallon" Or _
.Cells(Lrow, "C").Value = "Gross-margin-target-$-total" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-total" Or _
.Cells(Lrow, "g").Value = "" Then .Rows(Lrow).Delete
'Or use this if you want to check more values.

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End With
End Sub

 

[Dave Peterson]

If IsError(.Cells(Lrow, "A").Value) _
or iserror(.cells(lrow,"C").value) _
or iserror(.cells(lrow,"G").value) then
'at least one error found
end if

If I understand correctly.

 

[Dave Peterson]

Inline.

Fantastic Dave. It works beatifully. Thanks a lot. This solves the core of my problem to format sheets of my workbook into "list" format that Access can use.

My next step is to to figure out how to use ADO to consolidate all my workseets into Access.

Some non-core questions for you and others that are not yet bored...

1)
Could you clarify what you mean by turning off the "on error resume next" lines. Am I to assume that you meant that GoTo 0 will help in the development process of the macro, but once the macro is finalized this line GoTo 0 should be deleted?

I mean that you can turn error checking off with "on error resume next", then do
the process that may cause the error--but turn back error checking as soon as
your done with that statement.

On error resume next
activesheet.pictures("hithere").delete
on error goto 0

If there isn't a picture named "hithere" on the activesheet, then that's ok with
me. I'll just ignore the error. But I want to make sure that I'm still
checking for errors when I do the next thing. So I give error handling back to
excel (on error goto 0).

You'd keep both those lines in your production code.

2)
This workbook has 30 worksheets. 25 have identical formats, 5 of them are all different. As a result, I get a Run-time error '13': Type mismatch, and the debugger highlights 7 lines of code that begins with


Should I insert between every of those 7 lines "On Error Resume Next" or some other error coding to avoid this problem?

I'd try to avoid those 5 worksheets. One easy way is to just look at the name
of the worksheet:

Dim sht As Worksheet
for each sht in activeworkbook.worksheets
if lcase(sht.name) = "sheet1" _
or lcase(sht.name) = "sheeta" _
or lcase(sht.name) = "mysheet" _
or lcase(sht.name) = "hithere2" _
or lcase(sht.nane) = "whatshappening" then
'do nothing
else
'do your real code
end if
next sht

You could also use the "select case" structure:

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
Select Case LCase(sht.Name)
Case Is = "sheet1", "sheeta", "mysheet", _
"hithere2", "whatshappening"
MsgBox "skip it"
Case Else
MsgBox "do it"
End Select
Next sht

3)
Please note that of course I can just delete these 5 worksheets before the formatting process begins, but then I wouldn't learn more about error coding.

Application.DisplayAlerts = False
Sheets("DontNeedSheet1").Delete
Sheets("DontNeedSheet2").Delete
Application.DisplayAlerts = True

How can I write some code that enacts a command to delete every worksheet name that does not end with the letters "EP"? That would be more fun to do I think.

One way...

dim sht as worksheet
for each sht in activeworkbook.worksheets
if right(lcase(sht.name),2) = "ep" then
'do nothing
else
If ActiveWorkbook.Sheets.Count > 1 Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
end if
next sht

I see that you turned off alerts, deleted the sheet, then turned them back on.
Same as the "on error" stuff!

And if you try to that last sheet, you'll get an error. So you can just check
to see how many sheets are still existing before you try.

Another way is to use Like:

Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
If LCase(sht.Name) Like "*ep" Then
'do nothing
Else
If ActiveWorkbook.Sheets.Count > 1 Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
End If
Next sht

4)
I am also wondering how I can duplicate this workbook first, and then have the macro reformat the duplicated workbook? I adjusted the following from a post by Don Guillett and will try to implement it tomorrow.

With ActiveWorkbook
MyWB = .Path.Name
.SaveCopyAs MyWB
.Save
End With

dim mySavedCopy as workbook
dim myFileName as string
myfilename = "c:\mypath\....\whateveryouwanthere.xls"

with activeworkbook
.savecopyas filename:=myfilename
end with
'then just reopen it

set mysavedcopy = workbooks.open(filename:=myfilename)

======
(Not sure why you saved the original in Don's example.)

Thanks again Dave. Thanks again all. I appreciate your help very much.

Regards,
SteveC

Good luck with the ADO stuff. I don't speak the ADO, but there's lots of people
who do who hang around here.

 

[SteveC]

Dave, thanks again for all your time. That was a long request and I really didn't expect a response. I am very grateful. I think this will help a lot of people looking to transform their spreadsheets into a list (tabular) format that can be used by a database program like Access.

I took your advice and purchased Walkenbach's Excel 2003 Power Programming with VBA. Much simpler than the VBA book published by Wrox but good enough to give me some basic fundamentals. I'm going to keep the Wrox title as well since it appears I'll be ready for it once I'm finished with Walkenbach's. I think my next VBA question will be much more informed.

Thanks again. I look forward to reading your future posts. Live long and prosper.
Steve

 

[Dave Peterson]

xl2k???? You were truncated!

Put your cursor on open (in workbooks.open).
Hit F1.

You'll see that you can specify how you want to handle links right there.

 

[Andrew]

Well I never knew that - thats a handy little tip.
Thanks

xl2k???? You were truncated!

Put your cursor on open (in workbooks.open).
Hit F1.

You'll see that you can specify how you want to handle links right there.

I've read this is only possible in Excel 2002 and above, but just wanted to
get your take on it if possible. Thanks.

 

[SteveC]

Hello Dave and all,

UpdateLinks Issue:

Set mySavedCopy = Workbooks.Open("C:\Documents and Settings\My Documents\Steve\Formatted.xls", 0)

That is, in all the permutations I use trying to keep it close to your original
Set mySavedCopy = Workbooks.Open(FileName:=myFileName)
I get errors.

The following two return Run time errors 1004. These are the only permutations that don’t turn “red†in the module as I write them.
Set mySavedCopy = Workbooks.Open("FileName:=myFileName", 0)
Set mySavedCopy = Workbooks.Open("(FileName:=myFileName)", 0)

Is there a solution that will let me keep “FileName:=myFileName†nomenclature to prevent update of links?

 

[Dave Peterson]

maybe:

Set mysavedcopy = workbooks.open(filename:=myfilename, updatelinks:=0)

Does myfilename include the path?

 

[Dave Peterson]

Do you have any other workbooks open? It is an application setting.

No formulas--not even the links???

 

[SteveC]

That worked! F1 had nothing on that. Yes, filename includes the path.

 

[SteveC]

After I read your message I changed the code and added ".close (false)"
after the line telling the original to save a copy of itself. That fixed the
problem! Yes, I did have 2 workbooks open. The original workbook, and the
dupliate copy. But that extra line of code doesn't make it an issue anymore.


So no, no formulas no links. I got rid of all those links by deleting all
defined names (because I was linking to defined names).

Thanks again!

Steve

 

[Dave Peterson]

Be a little careful getting rid of all your names. Excel uses some for its own
purposes and you could be breaking stuff.

Download Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.ie/mvp

Then you can see if you can limit your deleting of names a little bit.

 

[SteveC]

Ok, thanks for your help and advice. I'll just delete 2 defined names that
are linked to a different workbook, and that should take care of it... What
an improvement from its start, though, eh...

 

[Dave Peterson]

But get Jan Karel's name manager utility. It's one of those essential tools
that you'll find you can't live without.