| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For..Next Problem
i'm having difficulty getting the code below to do what i want. the intent
is to enter the month once and have the program retain that value as it cycles through the numbered worksheets and performs the appropriate subroutine. i've tried a couple of approaches but either it doesn't retain the value on subsequent worksheets or i can't get past the For Each..Next - it doesn't recognize the For Each portion when it gets to the end. Any thoughts would be appreciated. Thanks, Alan ____ Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ActiveSheet.Name) Then If prepMonth = "November" Then Prepare_Nov Else If prepMonth = "December" Then Prepare_Dec Else If prepMonth = "January" Then Prepare_Jan Else If prepMonth = "February" Then Prepare_Feb Else If prepMonth = "March" Then Prepare_Mar Else If prepMonth = "April" Then Prepare_Apr Else If prepMonth = "May" Then Prepare_May Else If prepMonth = "June" Then Prepare_Jun Else If prepMonth = "July" Then Prepare_Jul Else If prepMonth = "August" Then Prepare_Aug Else If prepMonth = "September" Then Prepare_Sep Else MsgBox "There was a problem loading the data." End If End If End If End If End If End If End If End If End If End If End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For..Next Problem
Alan
Declare Prepmonth as Public and take the declaration out of your code and you can pass the value to your Subs. While your code may work I'd suggest you look at select case instead of all those nested ifs. Public prepmonth As String Sub Prepare_All_Worksheets2() 'Dim prepmonth As String ' delete this lline Mike "Alan P" wrote: i'm having difficulty getting the code below to do what i want. the intent is to enter the month once and have the program retain that value as it cycles through the numbered worksheets and performs the appropriate subroutine. i've tried a couple of approaches but either it doesn't retain the value on subsequent worksheets or i can't get past the For Each..Next - it doesn't recognize the For Each portion when it gets to the end. Any thoughts would be appreciated. Thanks, Alan ____ Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ActiveSheet.Name) Then If prepMonth = "November" Then Prepare_Nov Else If prepMonth = "December" Then Prepare_Dec Else If prepMonth = "January" Then Prepare_Jan Else If prepMonth = "February" Then Prepare_Feb Else If prepMonth = "March" Then Prepare_Mar Else If prepMonth = "April" Then Prepare_Apr Else If prepMonth = "May" Then Prepare_May Else If prepMonth = "June" Then Prepare_Jun Else If prepMonth = "July" Then Prepare_Jul Else If prepMonth = "August" Then Prepare_Aug Else If prepMonth = "September" Then Prepare_Sep Else MsgBox "There was a problem loading the data." End If End If End If End If End If End If End If End If End If End If End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For..Next Problem
Not usure if these changes will solve allyou problems because you didn't post
you other routines. The code below will work. If you are still having problems look at the rest of your code. Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ws.Name) Then select case preMonth Case "November" Prepare_Nov Case "December" Prepare_Dec Case "January" Prepare_Jan Case "February" Prepare_Feb Case "March" Prepare_Mar Case"April" Prepare_Apr Case "May" Prepare_May Case "June" Prepare_Jun Case "July" Prepare_Jul case "August" Prepare_Aug Case "September" Prepare_Sep Case Else msgbox("Incoreect Month - Exiting") exit sub else MsgBox "There was a problem loading the data." End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If "Alan P" wrote: i'm having difficulty getting the code below to do what i want. the intent is to enter the month once and have the program retain that value as it cycles through the numbered worksheets and performs the appropriate subroutine. i've tried a couple of approaches but either it doesn't retain the value on subsequent worksheets or i can't get past the For Each..Next - it doesn't recognize the For Each portion when it gets to the end. Any thoughts would be appreciated. Thanks, Alan ____ Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ActiveSheet.Name) Then If prepMonth = "November" Then Prepare_Nov Else If prepMonth = "December" Then Prepare_Dec Else If prepMonth = "January" Then Prepare_Jan Else If prepMonth = "February" Then Prepare_Feb Else If prepMonth = "March" Then Prepare_Mar Else If prepMonth = "April" Then Prepare_Apr Else If prepMonth = "May" Then Prepare_May Else If prepMonth = "June" Then Prepare_Jun Else If prepMonth = "July" Then Prepare_Jul Else If prepMonth = "August" Then Prepare_Aug Else If prepMonth = "September" Then Prepare_Sep Else MsgBox "There was a problem loading the data." End If End If End If End If End If End If End If End If End If End If End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For..Next Problem
What about this approach? prepMonth = InputBox("What is the month you are preparing for?") monthnum = Str(Month(DateValue("2008." & x & ".1"))) Worksheets(monthnum).Select prepare (monthnum) This way you can directly select the desired worksheet, don't need a cycle. I think you don't need 12 subs for months, you can use the same one with a month parameter. Regards, Stefi €žAlan P€ť ezt Ă*rta: i'm having difficulty getting the code below to do what i want. the intent is to enter the month once and have the program retain that value as it cycles through the numbered worksheets and performs the appropriate subroutine. i've tried a couple of approaches but either it doesn't retain the value on subsequent worksheets or i can't get past the For Each..Next - it doesn't recognize the For Each portion when it gets to the end. Any thoughts would be appreciated. Thanks, Alan ____ Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ActiveSheet.Name) Then If prepMonth = "November" Then Prepare_Nov Else If prepMonth = "December" Then Prepare_Dec Else If prepMonth = "January" Then Prepare_Jan Else If prepMonth = "February" Then Prepare_Feb Else If prepMonth = "March" Then Prepare_Mar Else If prepMonth = "April" Then Prepare_Apr Else If prepMonth = "May" Then Prepare_May Else If prepMonth = "June" Then Prepare_Jun Else If prepMonth = "July" Then Prepare_Jul Else If prepMonth = "August" Then Prepare_Aug Else If prepMonth = "September" Then Prepare_Sep Else MsgBox "There was a problem loading the data." End If End If End If End If End If End If End If End If End If End If End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For..Next Problem
not sure how to verify whant they inout into the inout box but, if they type in
month name, all you should need is to type the first 3 letters: Sub test() Dim prepmonth As String Dim MacroToRun As String prepmonth = InputBox("What is the month you are preparing for?") MacroToRun = "prepare_" & prepmonth Application.Run "testbook2!" & MacroToRun ' change name of workbook End Sub -- Gary "Alan P" wrote in message ... i'm having difficulty getting the code below to do what i want. the intent is to enter the month once and have the program retain that value as it cycles through the numbered worksheets and performs the appropriate subroutine. i've tried a couple of approaches but either it doesn't retain the value on subsequent worksheets or i can't get past the For Each..Next - it doesn't recognize the For Each portion when it gets to the end. Any thoughts would be appreciated. Thanks, Alan ____ Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ActiveSheet.Name) Then If prepMonth = "November" Then Prepare_Nov Else If prepMonth = "December" Then Prepare_Dec Else If prepMonth = "January" Then Prepare_Jan Else If prepMonth = "February" Then Prepare_Feb Else If prepMonth = "March" Then Prepare_Mar Else If prepMonth = "April" Then Prepare_Apr Else If prepMonth = "May" Then Prepare_May Else If prepMonth = "June" Then Prepare_Jun Else If prepMonth = "July" Then Prepare_Jul Else If prepMonth = "August" Then Prepare_Aug Else If prepMonth = "September" Then Prepare_Sep Else MsgBox "There was a problem loading the data." End If End If End If End If End If End If End If End If End If End If End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For..Next Problem
these were supposed to be input, keyboard must have moved on me.<g
not sure how to verify what they input into the input box but, if they type in month name, all you should need is to type the first 3 letters: -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... not sure how to verify whant they inout into the inout box but, if they type in month name, all you should need is to type the first 3 letters: Sub test() Dim prepmonth As String Dim MacroToRun As String prepmonth = InputBox("What is the month you are preparing for?") MacroToRun = "prepare_" & prepmonth Application.Run "testbook2!" & MacroToRun ' change name of workbook End Sub -- Gary "Alan P" wrote in message ... i'm having difficulty getting the code below to do what i want. the intent is to enter the month once and have the program retain that value as it cycles through the numbered worksheets and performs the appropriate subroutine. i've tried a couple of approaches but either it doesn't retain the value on subsequent worksheets or i can't get past the For Each..Next - it doesn't recognize the For Each portion when it gets to the end. Any thoughts would be appreciated. Thanks, Alan ____ Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ActiveSheet.Name) Then If prepMonth = "November" Then Prepare_Nov Else If prepMonth = "December" Then Prepare_Dec Else If prepMonth = "January" Then Prepare_Jan Else If prepMonth = "February" Then Prepare_Feb Else If prepMonth = "March" Then Prepare_Mar Else If prepMonth = "April" Then Prepare_Apr Else If prepMonth = "May" Then Prepare_May Else If prepMonth = "June" Then Prepare_Jun Else If prepMonth = "July" Then Prepare_Jul Else If prepMonth = "August" Then Prepare_Aug Else If prepMonth = "September" Then Prepare_Sep Else MsgBox "There was a problem loading the data." End If End If End If End If End If End If End If End If End If End If End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For..Next Problem
Thanks to all for your help. this is a rush job and i wasn't sure if Case
would work, thus the If statements. no awards for code excellence around here. thanks again. Alan "Gary Keramidas" wrote: these were supposed to be input, keyboard must have moved on me.<g not sure how to verify what they input into the input box but, if they type in month name, all you should need is to type the first 3 letters: -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... not sure how to verify whant they inout into the inout box but, if they type in month name, all you should need is to type the first 3 letters: Sub test() Dim prepmonth As String Dim MacroToRun As String prepmonth = InputBox("What is the month you are preparing for?") MacroToRun = "prepare_" & prepmonth Application.Run "testbook2!" & MacroToRun ' change name of workbook End Sub -- Gary "Alan P" wrote in message ... i'm having difficulty getting the code below to do what i want. the intent is to enter the month once and have the program retain that value as it cycles through the numbered worksheets and performs the appropriate subroutine. i've tried a couple of approaches but either it doesn't retain the value on subsequent worksheets or i can't get past the For Each..Next - it doesn't recognize the For Each portion when it gets to the end. Any thoughts would be appreciated. Thanks, Alan ____ Sub Prepare_All_Worksheets2() If MsgBox("Are you sure you want to move current forecast data to the prior section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo, "Move Forecast Data") = vbYes Then Dim prepMonth As String prepMonth = InputBox("What is the month you are preparing for?") Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select If IsNumeric(ActiveSheet.Name) Then If prepMonth = "November" Then Prepare_Nov Else If prepMonth = "December" Then Prepare_Dec Else If prepMonth = "January" Then Prepare_Jan Else If prepMonth = "February" Then Prepare_Feb Else If prepMonth = "March" Then Prepare_Mar Else If prepMonth = "April" Then Prepare_Apr Else If prepMonth = "May" Then Prepare_May Else If prepMonth = "June" Then Prepare_Jun Else If prepMonth = "July" Then Prepare_Jul Else If prepMonth = "August" Then Prepare_Aug Else If prepMonth = "September" Then Prepare_Sep Else MsgBox "There was a problem loading the data." End If End If End If End If End If End If End If End If End If End If End If Next ws Else 'USER CANCELLED MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move Forecast Data" End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
| Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
| problem with a conditional max problem | Excel Discussion (Misc queries) | |||
| Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming | |||
Linear Mode
