Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro Quandry
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Desiree
 
Posts: n/a
Default Macro Quandry
I am attempting to run a multi-step If-Then macro. Please see below, it
never gets past the first If statement line.

Sub Forecast()
'
' Forecast Macro
' Macro recorded 07/27/2005 by D. Baron
'

'
If B5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Oct_Forecast"
ElseIf C5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Nov_Forecast"
ElseIf D5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Dec_Forecast"
ElseIf E5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jan_Forecast"
ElseIf F5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Feb_Forecast"
ElseIf G5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Mar_Forecast"
ElseIf H5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Apr_Forecast"
ElseIf I5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!May_Forecast"
ElseIf J5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jun_Forecast"
ElseIf K5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jul_Forecast"
ElseIf L5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Aug_Forecast"
ElseIf M5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Sep_Forecast"
End If
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Henry
 
Posts: n/a
Default
Desiree,

Try something like this:

Sub Forecast()
Workbooks.Open ("SSA Business Review Tool_rev4.xls")

With ActiveSheet

Select Case Range("G1").Value

Case Range("B5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Oct_Forecast").Select

Case Range("C5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Nov_Forecast").Select

Case Range("D5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Dec_Forecast").Select

"""""""""""""""""""""""""""""""""""""""""""""" "
Etc.,Etc.

Case Range("M5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Sep_Forecast").Select

End Select
End With
End Sub


Henry


"Desiree" wrote in message
...
I am attempting to run a multi-step If-Then macro. Please see below, it
never gets past the first If statement line.

Sub Forecast()
'
' Forecast Macro
' Macro recorded 07/27/2005 by D. Baron
'

'
If B5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Oct_Forecast"
ElseIf C5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Nov_Forecast"
ElseIf D5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Dec_Forecast"
ElseIf E5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jan_Forecast"
ElseIf F5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Feb_Forecast"
ElseIf G5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Mar_Forecast"
ElseIf H5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Apr_Forecast"
ElseIf I5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!May_Forecast"
ElseIf J5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jun_Forecast"
ElseIf K5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jul_Forecast"
ElseIf L5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Aug_Forecast"
ElseIf M5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Sep_Forecast"
End If
End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Desiree
 
Posts: n/a
Default
Just an FYI... This is what ended up working. Thank you so much for your help.

With ActiveSheet

Select Case Range("G1").Value

Case Range("B5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Oct_Forecast"

Case Range("C5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Nov_Forecast"

Case Range("D5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Dec_Forecast"

Case Range("E5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Jan_Forecast"

Case Range("F5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Feb_Forecast"

Case Range("G5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Mar_Forecast"

Case Range("H5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Apr_Forecast"

Case Range("I5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!May_Forecast"

Case Range("J5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Jun_Forecast"

Case Range("K5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Jul_Forecast"

Case Range("L5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Aug_Forecast"

Case Range("M5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Sep_Forecast"
End Select
End With
End Sub



"Henry" wrote:

Desiree,

Try something like this:

Sub Forecast()
Workbooks.Open ("SSA Business Review Tool_rev4.xls")

With ActiveSheet

Select Case Range("G1").Value

Case Range("B5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Oct_Forecast").Select

Case Range("C5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Nov_Forecast").Select

Case Range("D5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Dec_Forecast").Select

"""""""""""""""""""""""""""""""""""""""""""""" "
Etc.,Etc.

Case Range("M5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Sep_Forecast").Select

End Select
End With
End Sub


Henry


"Desiree" wrote in message
...
I am attempting to run a multi-step If-Then macro. Please see below, it
never gets past the first If statement line.

Sub Forecast()
'
' Forecast Macro
' Macro recorded 07/27/2005 by D. Baron
'

'
If B5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Oct_Forecast"
ElseIf C5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Nov_Forecast"
ElseIf D5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Dec_Forecast"
ElseIf E5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jan_Forecast"
ElseIf F5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Feb_Forecast"
ElseIf G5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Mar_Forecast"
ElseIf H5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Apr_Forecast"
ElseIf I5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!May_Forecast"
ElseIf J5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jun_Forecast"
ElseIf K5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jul_Forecast"
ElseIf L5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Aug_Forecast"
ElseIf M5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Sep_Forecast"
End If
End Sub



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Make Alignment options under format cells available as shortcut dforrest Excel Discussion (Misc queries) 1 July 14th 05 10:58 PM
macro quandry.....?? Daesthai Excel Discussion (Misc queries) 2 June 29th 05 07:29 PM
Help with macro looping and color query function kevinm Excel Discussion (Misc queries) 10 May 26th 05 01:25 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 10:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 12:40 AM


All times are GMT +1. The time now is 05:52 PM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement