PDA

View Full Version : Solved: VBA Code . Max of 4 nos and min of 4 nos

nitzbar
08-11-2008, 11:32 AM
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.:help

Thanks...

xld
08-11-2008, 11:51 AM
MaxNum = Application.Max(Num1, Num2, Num3, Num4)

nitzbar
08-11-2008, 11:57 AM
thanks.... any tips for the min of 4 nos ? ignoring zeros and negative numbers?

xld
08-11-2008, 12:04 PM
Will it ba range values or variables?

Kenneth Hobs
08-11-2008, 12:17 PM
As xld said, it makes a difference for the input type. For ranges:
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

nitzbar
08-11-2008, 12:24 PM
variables... not a range...

xld
08-11-2008, 12:38 PM
sValues = "{" & Num1 & "," & Num2 & "," & Num3 & "," & Num4 & "}"

MinNum = ActiveSheet.Evaluate("MIN(IF(" & sValues & ">0," & sValues & "))")

nitzbar
08-13-2008, 06:57 AM
Thanks a lot.