For/Next Loop through worksheets
 

Hi,

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

For/Next Loop through worksheets
 

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name < "SUMMARY" and _
ws.Name < "Parameters" and _
ws.Name < "PIVOTDATA" and _
ws.Name < "PIV_Deliverables" and
ws.Name < "PIV_RC" Then
ws.Activate
Call Formatting
Next ws

Application.Goto "Summary_Home"

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi,

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

For/Next Loop through worksheets
 

You have 5 then's without corresponding end ifs. For what you are doing I
like to use select case statements. then I can control each worksheet as
necessary.

dim wks as worksheet

for each wks in Worksheets
Select Case wks.Name
Case "SUMMARY"
Case "Parameters"
Case "PIVOTDATA"
Case "PIV_Deliverables"
Case "PIV_RC"
Case Else
Call Formatting(wks)
End Select
Next wks

--
HTH...

Jim Thomlinson


"klysell" wrote:

Hi,

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

For/Next Loop through worksheets
 

Kent,

You need to have an "End If" for your If statements.. There are also some
other problems with your code.. Give this a try:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "SUMMARY", "Parameters", "PIVOTDATA", "PIV_Deliverables",
"PIV_RC"
'do nothing
Case Else
ws.Activate
Call Formatting
End Select
Next ws



--
Hope that helps.

Vergel Adriano


"klysell" wrote:

Hi,

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

For/Next Loop through worksheets
 

You are getting that error because you have 5 If-Then statements without any
corresponding End If statements to complete the blocks. The "Next without
For" error is bogus... it has to do with the way VB manages blocks of code
(irrelevant error messages like this is something you will have to get use
to). Anyway, your code should work if you add 5 individual End If statements
right after the Call Formatting statement.

Rick

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

For/Next Loop through worksheets
 

Thanks guys.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Vergel Adriano" wrote:

Kent,

You need to have an "End If" for your If statements.. There are also some
other problems with your code.. Give this a try:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "SUMMARY", "Parameters", "PIVOTDATA", "PIV_Deliverables",
"PIV_RC"
'do nothing
Case Else
ws.Activate
Call Formatting
End Select
Next ws



--
Hope that helps.

Vergel Adriano


"klysell" wrote:

Hi,

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

For/Next Loop through worksheets
 

one other error you had and an omitted underscore/continuation character on
my part:

I added the ws.Activate because I assume your formatting macro works on the
activesheet since you don't pass an argument to it.

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb.Worksheets '<-- add Worksheets
If ws.Name < "SUMMARY" and _
ws.Name < "Parameters" and _
ws.Name < "PIVOTDATA" and _
ws.Name < "PIV_Deliverables" and _
ws.Name < "PIV_RC" Then
ws.Activate
Call Formatting
Next ws

Application.Goto "Summary_Home"

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name < "SUMMARY" and _
ws.Name < "Parameters" and _
ws.Name < "PIVOTDATA" and _
ws.Name < "PIV_Deliverables" and
ws.Name < "PIV_RC" Then
ws.Activate
Call Formatting
Next ws

Application.Goto "Summary_Home"

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi,

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

For/Next Loop through worksheets
 

It's all good. Thanks Rick.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Rick Rothstein (MVP - VB)" wrote:

You are getting that error because you have 5 If-Then statements without any
corresponding End If statements to complete the blocks. The "Next without
For" error is bogus... it has to do with the way VB manages blocks of code
(irrelevant error messages like this is something you will have to get use
to). Anyway, your code should work if you add 5 individual End If statements
right after the Call Formatting statement.

Rick

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo He
Call Formatting

Next ws

He
Application.Goto "Summary_Home"


Thanks in advance!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557