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

 

[ndjustin20]

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.

I was just typing a response to that question as I got your answer. I was WRONG about the activation as I am using the variable from the For Each function to call sht.Range("A1").Value. I am referring to the array pointer by saying pointer. I'm still fairly new to VBA so there are some things that still throw me for a loop when I approach them. Thanks again for your help on this one. I told myself I wasn't going to learn anything today....and surrrrrrrrrrrrrrrrrrrrrrrrrre you come along Thank again !!!!!!

 

[Jonmo1]

Still not sure I follow...but it doesn't matter now since you have resolution.

Glad to help, thanks for the feedback.