Calculating an average using VBA

[Ben]

Hello, Is there a way of calculating the average (or the maximum) of a range
within VBA without actually entering the formula in the spreadshhet.
Supposing I want to assign to a variable, the average or the maximum of the
numbers that reside in Column C from row 5 to ro 25.

Thank you

 

[Mike H]

Try worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Target.Count > 1 Then Exit Sub
Set MyRange = Range("C5:C25")
If Not Intersect(Target, MyRange) Is Nothing Then
Range("A1").Value = WorksheetFunction.Max(MyRange)
Range("A2").Value = WorksheetFunction.Average(MyRange)
End If
End Sub


Mike

 

[Ben]

Thank you