# 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

#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 outputDim i As IntegerDim intOutRow As IntegerSub PerMonth()'Set you worksheetsSet s1 = Worksheets("sheet1")Set s2 = Worksheets("sheet2")'Initialise variables the first date in your setintOutRow = 1 'Excel is not zero-basedintYear = Year(s1.Cells(1, 1).Value)intMonth = Month(s1.Cells(1, 1).Value)'Loop through your dataFor 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 IfNext iEnd 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