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

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" wrote:

> 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

Thank you

"Ben" wrote:

> 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