If...then...else Statement

[Smurfit-Stone]

Hello Board,
I recorded a macro and now I need to tweek it to include a If statement.
I have a summary sheet and various other sheets that the information on the summary sheet pulls from. I want to use a If, Then, Else statement to look at the value in A2, (month). If its August then select cells B4:G4 and insert a formula {(=Smith!AugustSales)} else if its September insert formula {(Smith!SeptemberSales)}, else if its October insert ({Smith!OctoberSales)} and so on till next July. Can anyone help, I'm running out of time....Thanks!

 

[Jonmo1]

Can you post the code you have so far. It should be doable, without even an IF statement. If 1 of the 2 following are true :

1. A2 is an actual DATE, formatted to show the month...
2. A2 is TEXT that matches exactly how the months are spelled in the named ranges.

 

[Smurfit-Stone]

Here's my macro and it only work for the first line. It puts in Augusts total no matter what I put in A3. Can anyone tell me what's wrong with this If, Then, Else statement

 

[Smurfit-Stone]

Here's my macro and it only work for the first line. It puts in Augusts total no matter what I put in A3. Can anyone tell me what's wrong with this If, Then, Else statement? I recorded the steps and to get the Selection.FormulaArray part, and pieced the If, Else, then part together form excel help, but it only performs the first part and if I enter another month in A3 it still only performs the first part. HELP

Sub Sales()
If A3 = August Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!August"
ElseIf A3 = September Then
Range("i6:m6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!September"
ElseIf A3 = October Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!October"
ElseIf A3 = November Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!November"
ElseIf A3 = December Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!December"
ElseIf A3 = January Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!January"
ElseIf A3 = February Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!February"
ElseIf A3 = March Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!March"
ElseIf A3 = April Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!April"
ElseIf A3 = May Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!May"
ElseIf A3 = June Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast August 2007.xls'!June"
ElseIf A3 = July Then
Range("B6:G6").Select
Selection.FormulaArray = "='Forecast July 2007.xls'!July"
End If
End Sub

 

[Norie]

What exactly are August, September etc?

 

[mordrid]

I would have thought CASE would have been better than If Then Else but someone else will need to advise as unsure of exactly the code that would be needed

 

[Jonmo1]

How about just 1 line...
No IF ELSE, or even Case Select needed....
as long as A3 is TEXT January, February etc....

Range("B6:G6").FormulaArray = "='Forecast August 2007.xls'!" & Range("A3").Value

 

[Smurfit-Stone]

I tried this 1 line of code Jonmo1 and got the error message "Object doesn't support this property or method" What I have in the module is
Sub Sales()
Range("B6:G6").FormulaArray = "='Forecast August 2007.xls'!" & Range("A3").Value
End Sub

Plus this is a summary sheet of many sheets for salesmen. Each salesman has a sheet with 12 months of sales data. Each month total is a 6 column range with the month name...ie. Smith's worksht, has Augusts totals and that range is named August, Barnhart's worksht has August Totals and that total range is named August, so you see I need to identify with worksheet the August totals coming from. The summary sheet will list all the salespeople and their month total. I want the totals to populate by entering the month in A3. That way when December comes I would enter December in A3 and it would go to each salesperson's worksht and get the range named December....I hope I'm making sense. Thanks!

 

[Smurfit-Stone]

I really need help. I have a workbook with 11 workshts, 10 of them are data for salesmen and their customers with totals for all customers. I've named the range for each column of totals (6) by month...ie August's totals are named AugustSales, September's totals are named SeptemberSales and so on. Each worksht is name for the salesperson...ie Smith, Barnhart etc. The 11th worksht is a summary of all ten salesmen so the manager doesn't have to look at each tab. I have listed down column A each salesperson's name and starting in column B:G I want to show their totals. Now in cell A3 I've made a place for the manager to enter the month, what I want is for him to be able to enter a month in A3 click a button that runs a macro and the macro populates the cells B:G with the totals from the other workshts. So if he enters September then the macros would go to each salesman's tab and bring back the 6 columns of totals for that month. Each of the salesmen's tabs list all twelve months in the year, but I would only want the summary to show the month listed in A3 of the summary tab. Thanks in advance and I hope this makes sense.

 

[Jonmo1]

This works for me.

Range("B6:G6").FormulaArray = "='Forecast August 2007.xls'!" & Range("A3").Value

But since your last post says your range names are monthSales, and not just the month... i.e.
JanuarySales
MarchSales
AprilSales
etc...

Instead of
January
March
April
etc.

You may need to add to the end..

Range("B6:G6").FormulaArray = "='Forecast August 2007.xls'!" & Range("A3").Value & "Sales"