Finding lowest non zero value in excel VBA

[raj]

How can i get the lowest non zero value among a set of integers within excel VBA ?

[s-o-s]

On 2002-05-20 09:25, raj wrote:
How can i get the lowest non zero value among a set of integers within excel VBA ?

You did not mention negative numbers assuming these are allowed the following will work.

Sub FindLowest()
IamTheLowest = 9E+50
LowAddr = ""
For Each c In Selection.Cells
    cv = c.Value
    ca = c.Address
    If cv < IamTheLowest And cv <> 0 Then
        IamTheLowest = cv
        LowAddr = ca
    End If
Next
MsgBox "Lowest Value is: " & IamTheLowest & Chr(10) & "In cell address: " & LowAddr
End Sub

If Negative numbers are not allowed.
then this will do it.

Sub FindLowest()
IamTheLowest = 9E+50
LowAddr = ""
For Each c In Selection.Cells
    cv = c.Value
    ca = c.Address
    If cv < IamTheLowest And cv > 0 Then
        IamTheLowest = cv
        LowAddr = ca
    End If
Next
MsgBox "Lowest Value is: " & IamTheLowest & Chr(10) & "In cell address: " & LowAddr
End Sub

Hope this helps.

[Jay Petrulis]

Another option...

Sub test()
Dim x, Rng1

Set Rng1 = Range("A1:A100")
x = WorksheetFunction.Min(Rng1)
If x = 0 Then
    x = Evaluate("=MIN(IF(" _
    & Rng1.Address & ">0," _
    & Rng1.Address & "))")
End If
MsgBox x
End Sub

Bye,
Jay