Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hello,
The vba code below works as is. But, I need to make changes and since the code is so long it would take a while to make the neccessary changes. Each If statement is almost the same but I'll need to change the year. Can this code be combined to be more efficent? If not, is there a way to make make the changes using vba code? THE VBA CODE: Sub Select_Cal_Options_Macros_Preview() If Sheet4.Range("B1") = 2 Then Sheets("January 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 3 Then Sheets("February 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 4 Then Sheets("March 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 5 Then Sheets("April 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 6 Then Sheets("May 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 7 Then Sheets("June 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 8 Then Sheets("July 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 9 Then Sheets("August 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 10 Then Sheets("September 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 11 Then Sheets("October 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 12 Then Sheets("November 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 13 Then Sheets("December 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 14 Then Sheets("January 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 15 Then Sheets("February 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 16 Then Sheets("March 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 17 Then Sheets("April 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 18 Then Sheets("May 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 19 Then Sheets("June 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 20 Then Sheets("July 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 21 Then Sheets("August 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 22 Then Sheets("September 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 23 Then Sheets("October 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 24 Then Sheets("November 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 25 Then Sheets("December 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If End Sub Thank you for your help in advance, jfcby |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Maybe...
Dim myMonthName as string dim myVal as long myval = sheet4.range("b1").value if myval 1 _ and myval < 26 then myMonthName = format(dateserial(2006, (mval mod 12) - 1, 1), "MMMM") Sheets(mymonthname & " 2006 NORTH").Select Call SetSelect_CalOptions end if I just called the subroutine instead of using Application.run. And you may want to add some more validation--just in case. ps. Untested--watch for typos. jfcby wrote: Hello, The vba code below works as is. But, I need to make changes and since the code is so long it would take a while to make the neccessary changes. Each If statement is almost the same but I'll need to change the year. Can this code be combined to be more efficent? If not, is there a way to make make the changes using vba code? THE VBA CODE: Sub Select_Cal_Options_Macros_Preview() If Sheet4.Range("B1") = 2 Then Sheets("January 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 3 Then Sheets("February 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 4 Then Sheets("March 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 5 Then Sheets("April 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 6 Then Sheets("May 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 7 Then Sheets("June 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 8 Then Sheets("July 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 9 Then Sheets("August 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 10 Then Sheets("September 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 11 Then Sheets("October 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 12 Then Sheets("November 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 13 Then Sheets("December 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 14 Then Sheets("January 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 15 Then Sheets("February 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 16 Then Sheets("March 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 17 Then Sheets("April 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 18 Then Sheets("May 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 19 Then Sheets("June 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 20 Then Sheets("July 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 21 Then Sheets("August 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 22 Then Sheets("September 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 23 Then Sheets("October 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 24 Then Sheets("November 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 25 Then Sheets("December 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If End Sub Thank you for your help in advance, jfcby -- Dave Peterson |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I saw a typo!
mval mod 12 should be myVal mod 12 (I missed a Y) Dave Peterson wrote: Maybe... Dim myMonthName as string dim myVal as long myval = sheet4.range("b1").value if myval 1 _ and myval < 26 then myMonthName = format(dateserial(2006, (mval mod 12) - 1, 1), "MMMM") Sheets(mymonthname & " 2006 NORTH").Select Call SetSelect_CalOptions end if I just called the subroutine instead of using Application.run. And you may want to add some more validation--just in case. ps. Untested--watch for typos. jfcby wrote: Hello, The vba code below works as is. But, I need to make changes and since the code is so long it would take a while to make the neccessary changes. Each If statement is almost the same but I'll need to change the year. Can this code be combined to be more efficent? If not, is there a way to make make the changes using vba code? THE VBA CODE: Sub Select_Cal_Options_Macros_Preview() If Sheet4.Range("B1") = 2 Then Sheets("January 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 3 Then Sheets("February 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 4 Then Sheets("March 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 5 Then Sheets("April 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 6 Then Sheets("May 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 7 Then Sheets("June 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 8 Then Sheets("July 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 9 Then Sheets("August 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 10 Then Sheets("September 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 11 Then Sheets("October 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 12 Then Sheets("November 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 13 Then Sheets("December 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 14 Then Sheets("January 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 15 Then Sheets("February 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 16 Then Sheets("March 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 17 Then Sheets("April 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 18 Then Sheets("May 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 19 Then Sheets("June 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 20 Then Sheets("July 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 21 Then Sheets("August 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 22 Then Sheets("September 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 23 Then Sheets("October 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 24 Then Sheets("November 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 25 Then Sheets("December 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If End Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hello,
Thanks for all the help the codes work great! Thanks jfcby Dave Peterson wrote: I saw a typo! mval mod 12 should be myVal mod 12 (I missed a Y) Dave Peterson wrote: Maybe... Dim myMonthName as string dim myVal as long myval = sheet4.range("b1").value if myval 1 _ and myval < 26 then myMonthName = format(dateserial(2006, (mval mod 12) - 1, 1), "MMMM") Sheets(mymonthname & " 2006 NORTH").Select Call SetSelect_CalOptions end if I just called the subroutine instead of using Application.run. And you may want to add some more validation--just in case. ps. Untested--watch for typos. jfcby wrote: Hello, The vba code below works as is. But, I need to make changes and since the code is so long it would take a while to make the neccessary changes. Each If statement is almost the same but I'll need to change the year. Can this code be combined to be more efficent? If not, is there a way to make make the changes using vba code? THE VBA CODE: Sub Select_Cal_Options_Macros_Preview() If Sheet4.Range("B1") = 2 Then Sheets("January 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 3 Then Sheets("February 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 4 Then Sheets("March 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 5 Then Sheets("April 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 6 Then Sheets("May 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 7 Then Sheets("June 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 8 Then Sheets("July 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 9 Then Sheets("August 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 10 Then Sheets("September 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 11 Then Sheets("October 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 12 Then Sheets("November 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 13 Then Sheets("December 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 14 Then Sheets("January 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 15 Then Sheets("February 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 16 Then Sheets("March 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 17 Then Sheets("April 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 18 Then Sheets("May 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 19 Then Sheets("June 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 20 Then Sheets("July 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 21 Then Sheets("August 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 22 Then Sheets("September 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 23 Then Sheets("October 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 24 Then Sheets("November 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 25 Then Sheets("December 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If End Sub Thank you for your help in advance, jfcby -- Dave Peterson -- Dave Peterson |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
If you don't like the way Dave's code looks, try this one. It is not as
compact, but it works. Sub rnMcro() Mnth = Array("January2006North", "February2006North", "March2006North",_ "April2006North", "May2006North", "June2006North",_ "July2006North", "August2006North", "September2006North",_ "October2006North", "November2006North", "December2006North",_ "January2006South", "February2006South", "March2006South",_ "April2006South", "May2006South", "June2006South",_ "July2006South", "August2006South", "September2006South",_ "October2006South", "November2006South", "December2006South") For c = 2 To 25 With Worksheets(3) If .Range("B1") = c Then Sheets(Mnth(c - 2)).Select Application.Run ("SetSelect_CalOptions") End If End With Next End Sub "jfcby" wrote: Hello, The vba code below works as is. But, I need to make changes and since the code is so long it would take a while to make the neccessary changes. Each If statement is almost the same but I'll need to change the year. Can this code be combined to be more efficent? If not, is there a way to make make the changes using vba code? THE VBA CODE: Sub Select_Cal_Options_Macros_Preview() If Sheet4.Range("B1") = 2 Then Sheets("January 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 3 Then Sheets("February 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 4 Then Sheets("March 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 5 Then Sheets("April 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 6 Then Sheets("May 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 7 Then Sheets("June 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 8 Then Sheets("July 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 9 Then Sheets("August 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 10 Then Sheets("September 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 11 Then Sheets("October 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 12 Then Sheets("November 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 13 Then Sheets("December 2006 NORTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 14 Then Sheets("January 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 15 Then Sheets("February 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 16 Then Sheets("March 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 17 Then Sheets("April 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 18 Then Sheets("May 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 19 Then Sheets("June 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 20 Then Sheets("July 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 21 Then Sheets("August 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 22 Then Sheets("September 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 23 Then Sheets("October 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 24 Then Sheets("November 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If If Sheet4.Range("B1") = 25 Then Sheets("December 2006 SOUTH").Select Application.Run ("SetSelect_CalOptions") End If End Sub Thank you for your help in advance, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Data Validation in Excel 2000 - Can 2 items from a list be combine | Excel Discussion (Misc queries) | |||
EXcel 2000 IF-AND Statement | Excel Discussion (Misc queries) | |||
How do I combine five EXCEL 2003 workbooks into one? | Excel Discussion (Misc queries) | |||
How do I combine five Excel 2003 workbooks? | Excel Worksheet Functions | |||
How do you run a Macro from an IF statement in Excel 2000? | Excel Worksheet Functions |