Function Min(ParamArray values() As Variant) As Variant
Dim minValue, Value As Variant
minValue = values(0)
For Each Value In values
If Value < minValue Then minValue = Value
Next
Min = minValue
End Function
Just to be complete, for Max, just reverse the inequality comparitor:
Code:If Value > minValue Then minValue = Value
Function Min(ParamArray values() As Variant) As Variant
Dim minValue, Value As Variant
minValue = values(0)
For Each Value In values
If Value < minValue Then minValue = Value
Next
Min = minValue
End Function
Function Max(ParamArray values() As Variant) As Variant
Dim maxValue, Value As Variant
maxValue = values(0)
For Each Value In values
If Value > maxValue Then maxValue = Value
Next
Max = maxValue
End Function
Try ThisHow to apply these functions to a Range?
Function minMax(ByVal rRange As Range, MinOrMax As String) As Double
Dim dMin As Double
Dim dMax As Double
Dim lLastRow As Long
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.ActiveSheet
lLastRow = ws1.Cells(rRange.Row, rRange.Column).End(xlDown).Row
dMin = ws1.Cells(rRange.Row, rRange.Column).Value
dMax = dMin
For Each cell In rRange.Cells
If cell.Value < dMin Then dMin = cell.Value
If cell.Value > dMax Then dMax = cell.Value
Next cell
If InStr(1, MinOrMax, "min") = 1 Then
minMax = dMin
Else
minMax = dMax
End If
End Function