Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 121
Default If statement combine, Excel 2000 & 2003

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default If statement combine, Excel 2000 & 2003

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default If statement combine, Excel 2000 & 2003

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 121
Default If statement combine, Excel 2000 & 2003

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,986
Default If statement combine, Excel 2000 & 2003

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation in Excel 2000 - Can 2 items from a list be combine RShaw Excel Discussion (Misc queries) 0 January 20th 09 08:40 PM
EXcel 2000 IF-AND Statement LPS Excel Discussion (Misc queries) 3 September 24th 08 05:36 PM
How do I combine five EXCEL 2003 workbooks into one? Robert Judge Excel Discussion (Misc queries) 3 October 9th 06 04:57 PM
How do I combine five Excel 2003 workbooks? Robert Judge Excel Worksheet Functions 2 October 9th 06 04:53 PM
How do you run a Macro from an IF statement in Excel 2000? Rosey Excel Worksheet Functions 1 April 27th 06 04:55 PM


All times are GMT +1. The time now is 07:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"