Iterate through sheets, get month, do math and output

[ndjustin20]

Hey Guys,

I have some code:

Public jan As Double
Public feb As Double
Public mar As Double
Public apr As Double
Public may As Double
Public jun As Double
Public jul As Double
Public aug As Double
Public sep As Double
Public oct As Double
Public nov As Double
Public dec As Double

Sub findWhatMonth()
jan = 0
For Each sht In ActiveWorkbook.Worksheets  'Iterate through all worksheets first

Dim dateFromA1 As Date        'Get the Date from cell A1 in each worksheet
Dim monthNumber As Integer    'Set a variable to catch the months number 1 - 12

dateFromA1 = Range("A1").Value  'This is setting the variable to the date in A1

monthNumber = Month(dateFromA1)     'This is using the Month function to change the date into an integer 1 -12

Call doMath(monthNumber)        'Call the doMath function with the month as an integer

MsgBox ("Output  " & sht.Name & "   " & jan)   'Using to test

Next sht

End Sub
Sub doMath(Month As Integer)

Select Case Month

Case 1 'Check to see if its January

jan = jan + ActiveSheet.Range("gross").Value    'Add value of Active Sheets gross cell to the jan global variable

Case 2  'Check to see if its February

feb = feb + Range("gross").Value                'Add value of Active Sheets gross cell to the feb global variable

Case 3  'Check to see if its March

mar = mar + Range("gross").Value                'Add value of Active Sheets gross cell to the mar global variable

Case 4  'Check to see if its April

apr = apr + Range("gross").Value                'Add value of Active Sheets gross cell to the apr global variable

Case 5  'Check to see if its May

may = may + Range("gross").Value                'Add value of Active Sheets gross cell to the may global variable

Case 6  'Check to see if its June

jun = jun + Range("gross").Value                'Add value of Active Sheets gross cell to the jun global variable

Case 7  'Check to see if its July

jul = jul + Range("gross").Value                'Add value of Active Sheets gross cell to the jul global variable

Case 8  'Check to see if its August

aug = aug + Range("gross").Value                'Add value of Active Sheets gross cell to the aug global variable

Case 9  'Check to see if its September

sep = sep + Range("gross").Value                'Add value of Active Sheets gross cell to the sep global variable

Case 10  'Check to see if its October

oct = oct + Range("gross").Value                'Add value of Active Sheets gross cell to the oct global variable

Case 11  'Check to see if its November

nov = nov + Range("gross").Value                'Add value of Active Sheets gross cell to the nov global variable

Case 12  'Check to see if its December

dec = dec + Range("gross").Value                'Add value of Active Sheets gross cell to the dec global variable

End Select

End Sub

That I need some help with. I need to iterate through some sheets of a workbook and take information from each sheet if the sheet is January thru December. Running the attached code I keep getting the same result from only the first sheet. I used a named range on each sheet as gross. I am finding each named range named gross on each sheet and adding them together if the sheet is inside the same month.

Any help is appreciated.

Justin

 

[AlphaFrog]

If the date value is from cell A1 from each month sheet then use this...
dateFromA1 = sht.Range("A1").Value

 

[ndjustin20]

If the date value is from cell A1 from each month sheet then use this...
dateFromA1 = sht.Range("A1").Value

I wasn't sure why that would make any difference but I tried it anyways and it made no difference. Thank you for the suggestion but it made no difference at all. What keeps happening is the first sheets value just keeps on getting added though the next sheets have different values.

 

[Jonmo1]

Your doMath sub is also only referring to the Active sheet...NOT the sheet in your loop.

This is a common problem.
doing a For Each sht loop DOES NOT activate each sheet during the loop.
So whatever sheet happened to be active at the time the code ran 'remains active' throughout the loop.
And unless otherwise specified, all range commands refer to the Active Sheet.
So You have to refer to the sht variable in your code.

It's probably easier to explain what you're trying to do,
Are you trying to SUM a particular cell from each sheet?

 

[ndjustin20]

That's exactly what I am trying to do !!!!!!!!!! Can you tell me the most efficient way of doing this please? I assumed, as I know I shouldn't , that each pass would activate the sheet it was already looking through. What is odd is that is the case for the date I am pulling from a cell. Why is this different for a named range?? I can get the date from A1 on each pass currently and use that data to call the doMath function just fine. What I can't do though is get the value to add properly for some reason. I don't think it is a sheet activation issue as much as it is an addition issue with the variable. I added the month output just to make sure I was getting a different month on each pass through a few sheets and it does give me the value of the sheet that is being passed through without activating the sheet. Code is now:

Public jan As Double
Public feb As Double
Public mar As Double
Public apr As Double
Public may As Double
Public jun As Double
Public jul As Double
Public aug As Double
Public sep As Double
Public oct As Double
Public nov As Double
Public dec As Double

Sub findWhatMonth()
jan = 0
For Each sht In ActiveWorkbook.Worksheets  'Iterate through all worksheets first

Dim dateFromA1 As Date        'Get the Date from cell A1 in each worksheet
Dim monthNumber As Integer    'Set a variable to catch the months number 1 - 12

dateFromA1 = sht.Range("A1").Value  'This is setting the variable to the date in A1

monthNumber = Month(dateFromA1)     'This is using the Month function to change the date into an integer 1 -12

Call doMath(monthNumber)        'Call the doMath function with the month as an integer

MsgBox ("Output  " & sht.Name & "   " & jan & "  " & monthNumber)    'Using to test

Next sht

End Sub
Sub doMath(Month As Integer)

Select Case Month

Case 1 'Check to see if its January

jan = jan + ActiveSheet.Range("gross").Value    'Add value of Active Sheets gross cell to the jan global variable

Case 2  'Check to see if its February

feb = feb + Range("gross").Value                'Add value of Active Sheets gross cell to the feb global variable

Case 3  'Check to see if its March

mar = mar + Range("gross").Value                'Add value of Active Sheets gross cell to the mar global variable

Case 4  'Check to see if its April

apr = apr + Range("gross").Value                'Add value of Active Sheets gross cell to the apr global variable

Case 5  'Check to see if its May

may = may + Range("gross").Value                'Add value of Active Sheets gross cell to the may global variable

Case 6  'Check to see if its June

jun = jun + Range("gross").Value                'Add value of Active Sheets gross cell to the jun global variable

Case 7  'Check to see if its July

jul = jul + Range("gross").Value                'Add value of Active Sheets gross cell to the jul global variable

Case 8  'Check to see if its August

aug = aug + Range("gross").Value                'Add value of Active Sheets gross cell to the aug global variable

Case 9  'Check to see if its September

sep = sep + Range("gross").Value                'Add value of Active Sheets gross cell to the sep global variable

Case 10  'Check to see if its October

oct = oct + Range("gross").Value                'Add value of Active Sheets gross cell to the oct global variable

Case 11  'Check to see if its November

nov = nov + Range("gross").Value                'Add value of Active Sheets gross cell to the nov global variable

Case 12  'Check to see if its December

dec = dec + Range("gross").Value                'Add value of Active Sheets gross cell to the dec global variable

End Select

End Sub

 

[Jonmo1]

Is there more than 1 sheet for each month?

You might have 10 jan sheets, but only 7 feb sheets, then 15 mar sheets?

What are you wanting to actually DO with the resulting Sums ? Put them in another sheet? Which sheet, which cell(s) ..


I think you're overcomplicating it by splitting it into 2 subs. Having to pass all those variables between the two just isn't worth it.

I'd keep it all in one sub, and put the Select Case part inside the For Each Sht Loop.
And make sure ALL range statements are preceded with the sht. variable.

 

[ndjustin20]

Yes there is more than one sheet per month. So yes as stated you could have 10 January sheets and 7 February sheets and so on and so forth. I am wanting to put them in another sheet but can't do that until I can figure out why I can't iterate through some sheets and add one cell on each pass to a global variable. I'm sure the code can be more efficient though I need it to work before I start whittling it down to efficient. I've found it's more efficient to split up the code in VBA as it seems to error out less and it's easier to see where the errors are coming from so that is why I have it broken into two functions. Some people like to create a function that contains a thousand lines of code though I prefer to keep it broken into smaller functions for debugging issues and for easier diagramming of the overall application needed. Really just a preference I would say.

 

[Jonmo1]

OK, well the reason it's not working is because the ranges you wrote in the doMath sub do NOT refer to the sheets in the loop from the first sub.
They refer to whichever sheet is currently Active. The for each sht loop DOES NOT activate each sheet during it's loop.

You can pass the sht variable to the doMath Sub like this

Change
Call doMath(monthNumber)
to
Call doMath(monthNumber, sht)

And Change
Sub doMath(Month As Integer)
to
Sub doMath(Month As Integer, sht As Worksheet)


Then preced all Range references with the sht variable
sht.Range(...)

 

[ndjustin20]

OK, well the reason it's not working is because the ranges you wrote in the doMath sub do NOT refer to the sheets in the loop from the first sub.
They refer to whichever sheet is currently Active. The for each sht loop DOES NOT activate each sheet during it's loop.

You can pass the sht variable to the doMath Sub like this

Change
Call doMath(monthNumber)
to
Call doMath(monthNumber, sht)

And Change
Sub doMath(Month As Integer)
to
Sub doMath(Month As Integer, sht As Worksheet)


Then preced all Range references with the sht variable
sht.Range(...)

I tried what you said and got a type mismatch error. Looking closer at the code you are passing a variable of type worksheet and then trying to cast it as a type worksheet again. I took out the type mismatch so I removed the As Worsheet portion and the code ran with the end result as needed. I appreciate the help though I was completely confused as to why I needed to activate the sheet again until I realized I am losing scope when calling doMath outside of the original function and by calling the active sheet the pointer for some reason doesn't show it moved to the next sheet when i'm calling doMath. I'm still not sure why the pointer doesn't move and I have to specifically ask for the placement of the pointer in the Workbook array when I'm calling for a cell in the activesheet. I would think the activesheet would be the activesheet set by the pointer but outside of the original function it seems to lose scope altogether. Inside the original function I don't need to specify the pointers placement as with each pass I'm able to set a variable with a cell value without specifically asking for sht.WhateverRange and just by using Range(WhatEverCellNeeded).Value.

I can only assume at this point that I lose scope by going outside of the original function that set the workbook and worksheet variables as active in the beginning of the loop. Thank you so much for your help. I think I learned something today!!!!!!!!!!!!

 

[Jonmo1]

I don't follow, what is 'the pointer' ??

which part of the code worked as expected without referring to the sht variable?


All I know is that this
For Each sht In Worksheets

DOES NOT activate each sheet during that loop.