deppe.k
Dec 10th, 2009, 08:08 AM
I'm working on a Visual Basic macro for an excel document that computes averages. Data is dumped into excel and the macro calculates the average of the last 10 values per column. My problem is is that if there isn't 10 values per column, I get an error. I'd rather like to have the average of however many values there are. So if there are 9 or fewer values per column, I still want an average of those, but I'm stuck on the code. Any Suggestions? Thanks!
This is what I have to compute the average if there are 10 values:
'Start of Average Macro
Cells.Find(What:="timestamp", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
Do
Selection.Range("A3").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-11]C:R[-2]C)"
ActiveCell.Offset(-2, 1).Select
Loop While Abs(ActiveCell) > 0
This is what I have to compute the average if there are 10 values:
'Start of Average Macro
Cells.Find(What:="timestamp", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
Do
Selection.Range("A3").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-11]C:R[-2]C)"
ActiveCell.Offset(-2, 1).Select
Loop While Abs(ActiveCell) > 0