AVERAGE formula VBA

[SBF12345]

Greetings,

I am having a bit of trouble with my code. I am using the average function to find the average of the contents in a set of cells in a column. The function in the cell that contains the average function is =AVERAGE($H$5:$H$200).

When I run my code periodically I add a new row and a new number comes in at the top. When I get the new row of data the AVERAGE function in the cell described above changes to =AVERAGE($H$6:$H$201). I would like to continue to find the average for the most recent set of cells and not have the cells shift on me. I thought the absolute reference would accomplish this...apparently not. Any advice is appreciated.

 

[My Aswer Is This]

I see no Vba code here. Any time your using Vba code please always show it to us.

 

[SBF12345]

I see no Vba code here. Any time your using Vba code please always show it to us.

The cells with the AVERAGE Function are not included in the VBA. I was assuming they would adjust with the addition of new material.

Range("B5").Select    ActiveSheet.Paste
    Range("L6").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFill Destination:=Range("L6:AR5"), Type:=xlFillDefault

Now that I think about it I might need to run a loop with the function I need for each page, or something.

Im afraid the code isn't too helpful.

 

[My Aswer Is This]

If your looking for a Vba solution to average all the cells in say column "C"
And put that value in cell "A1" use this script:

Sub My_Average()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Cells(1, 1).Value = WorksheetFunction.Average(Range(Cells(1, 3), Cells(lastrow, 3)))
End Sub

 

[SBF12345]

If your looking for a Vba solution to average all the cells in say column "C"
And put that value in cell "A1" use this script:

Sub My_Average()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Cells(1, 1).Value = WorksheetFunction.Average(Range(Cells(1, 3), Cells(lastrow, 3)))
End Sub

Thanks for the help. I think I figured it out!