Solved: VBA Code . Max of 4 nos and min of 4 nos

[nitzbar]

Hi ! I need VBA code to find the minimum of 4 nos ( ignoring zeros) and the max of 4 numbers... need this in vba as I will be using it in a user-defined function.

Thanks...

[Bob Phillips]

[vba]

MaxNum = Application.Max(Num1, Num2, Num3, Num4)
[/vba]

[nitzbar]

thanks.... any tips for the min of 4 nos ? ignoring zeros and negative numbers?

[Bob Phillips]

Will it ba range values or variables?

[Kenneth Hobs]

As xld said, it makes a difference for the input type. For ranges:
[vba]Sub test()
[a1] = 5
[a2] = 0
[a3] = 4
[a4] = 1
MsgBox "Max=" & MaxMin(Range("A1:A4")) & vbCrLf & _
"Min=" & MaxMin(Range("A1:A4"), False)
End Sub

Function MaxMin(my4Nums As Range, Optional bMax As Boolean = True)
Dim r As Range, i As Integer, n() As Variant
i = 0
If bMax = False Then
For Each r In my4Nums
If r.Value <> 0 Then
i = i + 1
ReDim Preserve n(1 To i) As Variant
n(i) = r.Value
End If
Next r
MaxMin = WorksheetFunction.Min(n)
Exit Function
End If
MaxMin = WorksheetFunction.Max(my4Nums)
End Function

[/vba]

[nitzbar]

variables... not a range...

[Bob Phillips]

[vba]

sValues = "{" & Num1 & "," & Num2 & "," & Num3 & "," & Num4 & "}"

MinNum = ActiveSheet.Evaluate("MIN(IF(" & sValues & ">0," & sValues & "))")
[/vba]

[nitzbar]

Thanks a lot.