Articles

VBA macro to average

October 11, 2007 at 09:43:38
Specs: XP, Pent IV 1 GB

My VBA knowledge is very basic so looking more for an answer than help with developing the code.

I typically have data files with 1 data point per week day that will sometimes be 15 years long. I would like to have a macro that will give me a monthly average of the daily data for each month. Obviously I can do it manually, but going thru 15 years of daily data, with different number of week days in each month, to calculate a monthly average for each is a pain. Any suggestions or help would be greatly appreciated.


See More: VBA macro to average

Report •


#1
October 14, 2007 at 07:43:58


'Hello
'Havent posted here for ages! So I'll do it properly

'Try something like this
'Assuming your data is in two columns
'A = Date, B = Day-Traffic
'And you have two sheets, sheet1 (s1) and sheet2 (s2)
'Where sheet1 is your RAW data and 2 is output
Dim i As Integer
Dim intOutRow As Integer


Sub PerMonth()
'Set you worksheets
Set s1 = Worksheets("sheet1")
Set s2 = Worksheets("sheet2")
'Initialise variables the first date in your set
intOutRow = 1 'Excel is not zero-based
intYear = Year(s1.Cells(1, 1).Value)
intMonth = Month(s1.Cells(1, 1).Value)
'Loop through your data
For i = 1 To 3000 'or length of data
'Check if date has changes during loop
If Year(s1.Cells(i, 1).Value) = intYear And _
Month(s1.Cells(i, 1).Value) = intMonth Then
'Month is same so add to values in sheet 2
s2.Cells(intOutRow, 1).Value = Str(intYear) + " - " + Str(intMonth)
s2.Cells(intOutRow, 2).Value = s2.Cells(intOutRow, 2).Value + s1.Cells(i, 2).Value
Else
'Increment the output row and reassign Year and Month
intOutRow = intOutRow + 1
intYear = Year(s1.Cells(i, 1).Value)
intMonth = Month(s1.Cells(i, 1).Value)
'Month is now the same so add to values in sheet 2
s2.Cells(intOutRow, 1).Value = Str(intYear) + " - " + Str(intMonth)
s2.Cells(intOutRow, 2).Value = s2.Cells(intOutRow, 2).Value + s1.Cells(i, 2).Value
End If
Next i
End Sub
'Ok, this code could be severly reduced in size, but then it might lose it's lesson!
'In addition you could clear the data from sheet2 before posting to it?
'Thanks


Report •
Related Solutions


Ask Question