# If...then...else Statement

#### Smurfit-Stone

##### Active Member
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!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Jonmo1

##### MrExcel MVP
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

##### Active Member
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

##### Active Member
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

##### Well-known Member
What exactly are August, September etc?

#### mordrid

##### Board Regular
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

##### MrExcel MVP
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

##### Active Member
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

##### Active Member
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

##### MrExcel MVP
This works for me.
Code:
``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...

January
March
April
etc.

You may need to add to the end..

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

Replies
3
Views
292
Replies
5
Views
221
Replies
10
Views
267
Replies
6
Views
579
Replies
1
Views
250

1,191,366
Messages
5,986,244
Members
440,012
Latest member
StumpedGump1987

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back