This works for me when I call it in a cell with =Myprice5(A1,A,2,A3,A4)
Function MinPrice5(ParamArray arglist() As Variant) As Variant 'text or
number
Dim arg As Variant
Dim FoundANumber As Boolean
Dim myMin As Double
myMin = 100 ^ 100 'some really big number
FoundANumber = False
For Each arg In arglist
If IsNumeric(arg) Then
If arg 0 Then
FoundANumber = True
myMin = WorksheetFunction.Min(myMin, arg)
End If
End If
Next arg
If FoundANumber = True Then
MinPrice5 = myMin
Else
MinPrice5 = "Item Not Bid"
End If
End Function
But I prefer this since I can call it with =MinPrice4(A1:A10)
Function MinPrice4(arglist) As Variant 'text or number
Dim arg As Variant
Dim FoundANumber As Boolean
Dim myMin As Double
etc
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Bob K" wrote in message
...
The function below excludes cells with the value of #N/A. I am also
trying
to get it to exclude cells with a value of 0 (zero). Thus if I have 4
cells
with the follwing values:
#N/A
10
0
40
The min value that would be calculated would be 10.
thanks,
bob k
Function MinPrice4(ParamArray arglist() As Variant) As Variant 'text or
number
Dim arg As Variant
Dim FoundANumber As Boolean
Dim myMin As Double
myMin = 100 ^ 100 'some really big number
FoundANumber = False
For Each arg In arglist
If IsNumeric(arg) Then
FoundANumber = True
myMin = WorksheetFunction.Min(myMin, arg)
End If
Next arg
If FoundANumber = True Then
MinPrice4 = myMin
Else
MinPrice4 = "Item Not Bid"
End If
End Function
Sub aa()
MsgBox MinPrice4("a", 3, CVErr(xlErrNA))
End Sub