If statement combine, Excel 2000 & 2003

[jfcby]

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]

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.

 

[Dave Peterson]

I saw a typo!

mval mod 12
should be
myVal mod 12
(I missed a Y)

 

[Guest]

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]

Hello,

Thanks for all the help the codes work great!

Thanks
jfcby