I have a Big table of data which adds a new row for each record (per Day).
At the bottom for some columns I want to add the formula for 7-day average and 28-day average to some columns.
the following code does what I want but does not adapt to the addition of new Rows of data
Code
' Goto bottom of Report
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Range("A1").Select
ActiveCell.End(xlDown).Select
' add some Report summaries
ActiveCell.Offset(2, 10).Select
ActiveCell.Value = "(ignore 'Today' as incomplete) ..... 7-Day Average"
ActiveCell.HorizontalAlignment = xlRight
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "28-Day Average"
ActiveCell.HorizontalAlignment = xlRight
ActiveCell.Offset(-1, 1).Select
Range(ActiveCell, ActiveCell.Offset(1, 1)).HorizontalAlignment = xlCenter
ActiveCell.Formula = "=average(L261:L255)"
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=average(L261:L233)"
ActiveCell.Offset(-1, 1).Select
ActiveCell.Formula = "=average(M261:M255)"
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=average(M261:M233)"
Display More
I have researched and experimented with various alternatives to define the ranges to be averaged
Code
Sub zzz()
'ActiveCell.Formula = "=average(" & Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(-10, 0)) & ")"
'ActiveCell.Formula = "=average(" & ActiveCell.Offset(-3, 0) & ":" & ActiveCell.Offset(-10, 0) & ")"
'ActiveCell.Formula = "=average(" & Range(Cells.Offset(-3, 0), Cells.Offset(-10, 0)).Address(False, False) & ")"
Dim rng7 As Range
Set rng7 = Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(-10, 0))
ActiveCell.Formula = "=average(" & rng7 & ")"
' ActiveCell.Offset(0, 0).Value = WorksheetFunction.average(Range(ActiveCell.Offset(-3, 0).Address & ":" & ActiveCell.Offset(-10, 0).Address))
End Sub
Display More
but am unable to work out the proper syntax to define the ranges to be averaged so that they are not absolute addresses.
regards
Mark[ATTACH=CONFIG]73827[/ATTACH]