Error check and resume
 

After reading:

http://www.microsoft.com/office/comm...=&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

Error check and resume
 

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.

--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
After reading:


http://www.microsoft.com/office/comm...=&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

Error check and resume
 

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.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx
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

Error check and resume
 

Steve,

Change

With ActiveWorkbook.sht.UsedRange
to
With sht.UsedRange



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



"Steve" wrote in message
...
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.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx
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

Error check and resume
 

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





"Steve" wrote in message
...
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.

Regards,
Steve

"Chip Pearson" wrote:

Steve,

Change

With ActiveWorkbook.sht.UsedRange
to
With sht.UsedRange

Error check and resume
 

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



Steve wrote:

Thank you for your patience and help.

I think I just have one more question: How can I set sht to all worksheets in the workbook?

Also, now that you've seen my level of aptitude, I am wondering if you know of a book appropriate for my level to learn excel vba (walkenbach's book or a dummies book?).

Thanks again Chip. I really appreciate it.

--

Dave Peterson

another run-time error
 

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

another run-time error
 

This portion:

For Each sht In ActiveWorkbook.Worksheets
Next sht

is really:

For Each sht In ActiveWorkbook.Worksheets

'put all your code in here
'and I mean all your code goes here
'yep, all of it.

Next sht

===
I think that this will work:

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

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

For Each sht In ActiveWorkbook.Worksheets
With sht
.Select 'you have some stuff that needs it active
With .UsedRange
.Value = .Value
'why Selection here
'Selection.Interior.ColorIndex = xlNone
'Selection.Font.ColorIndex = 0
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
End With

ActiveWindow.FreezePanes = False

.Rows.Hidden = False
.Columns.Hidden = False

On Error Resume Next
.Rows.Ungroup
.Columns.Ungroup
On Error GoTo 0

On Error Resume Next
.Shapes("Drop Down 1").Cut
On Error GoTo 0

For Each cell In .Range("E1:E800").Cells
If IsError(cell.Value) Then
'do nothing
Else
cell.Value = Application.Trim(cell.Value)
End If
Next cell

Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows.Count + Firstrow - 1

.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 Lrow

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


I turned off a few "on error resume next" lines. If I'm gonna use them, I turn
them off right after I expect the error to occur.

On Error Resume Next
.Shapes("Drop Down 1").Cut
On Error GoTo 0

Then when something breaks, it's a little easier to find the problem.




SteveC wrote:

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

It's Alive! Alive!
 

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?

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

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

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

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.

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

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

Regards,
SteveC

If IsError(.Cells(Lrow, "A").Value) Then
 

Is there a way to use

If IsError(.Cells(Lrow, "A").Value) Then

to include not only column A, but Column C and G?

Thanks.

If IsError(.Cells(Lrow, "A").Value) Then
 

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.

SteveC wrote:

Is there a way to use

If IsError(.Cells(Lrow, "A").Value) Then

to include not only column A, but Column C and G?

Thanks.

--

Dave Peterson

It's Alive! Alive!
 

Inline.

SteveC wrote:

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

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

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" structu

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.

--

Dave Peterson

Thanks again, & noob opinion on excel vba books
 

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

Application.AskToUpdateLinks = False
 

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.



SteveC wrote:

First of all, I'm using Excel 200.

I added the first and third lines to your code.

Application.AskToUpdateLinks = False
Set mySavedCopy = Workbooks.Open(Filename:=myFileName)
mySavedCopy.Open.UpdateLink = False

The first line works, but the links still update.
I've been trying variations of "UpdateLink=False" in various configurations and locations in these three lines but I keep getting errors.

Any suggestions to prevent excel from updating links in the saved copy? I've read this is only possible in Excel 2002 and above, but just wanted to get your take on it if possible. Thanks.

Steve

--

Dave Peterson

Application.AskToUpdateLinks = False
 

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


"Dave Peterson" wrote in message
...
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.



SteveC wrote:

First of all, I'm using Excel 200.

I added the first and third lines to your code.

Application.AskToUpdateLinks = False
Set mySavedCopy = Workbooks.Open(Filename:=myFileName)
mySavedCopy.Open.UpdateLink = False

The first line works, but the links still update.
I've been trying variations of "UpdateLink=False" in various
configurations and locations in these three lines but I keep getting errors.

Any suggestions to prevent excel from updating links in the saved copy?
I've read this is only possible in Excel 2002 and above, but just wanted to
get your take on it if possible. Thanks.

Steve

--

Dave Peterson

UpdateLinks Issue
 

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 dont 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?

UpdateLinks Issue
 

maybe:

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

Does myfilename include the path?



SteveC wrote:

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 dont 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

Workbook Calculating, but there is nothing to calculate
 

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

No formulas--not even the links???

SteveC wrote:

Another separate issue:

Near the end of the code, If I change
With Application
.Calculation = CalcMode
.ScreenUpdating = True

to
With Application
.ScreenUpdating = True
.Calculation = CalcMode

I can see that it is spending time recalculating things. Why? There should be nothing to calculate because all my cells contain values! How can I fix this? Look for every cell that has a formula (which shouldnt exist anyway) and clear them?

I tried this
Sub DeleteUnused() from http://www.contextures.com/xlfaqApp.html#Unused
But it doesnt fix the problem.

Ive also tried Sub Delete_Rows() from Frank Kabel and David McRitchie, but I have not been able to adjust it to clear all cells with formulas from deleting all rows that have a cell with a formula:
http://www.microsoft.com/office/comm...=&ptlist=&exp=

On an as-is and standalone basis, Sub Delete_Rows() wouldnt work for me until I took out the
€œ+(Trim(.Value)=€�€�)
out of
€œIf .HasFormula + (Trim(.Value) = "") Then .EntireRow.Delete€�

Am I doomed?

--

Dave Peterson

UpdateLinks Issue
 

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


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

Does myfilename include the path?

Workbook Calculating, but there is nothing to calculate
 

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

Workbook Calculating, but there is nothing to calculate
 

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 wrote:

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

Workbook Calculating, but there is nothing to calculate
 

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...

Workbook Calculating, but there is nothing to calculate
 

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



SteveC wrote:

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