| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop worksheets | Excel Discussion (Misc queries) | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| (Complex) Loop within loop to create worksheets | Excel Programming | |||
| Loop worksheets | Excel Programming | |||
Hybrid Mode
