Help with function created in VBA
 

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

Help with function created in VBA
 

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

Help with function created in VBA
 

Bernard

thanks for your help!!

"Bernard Liengme" wrote:

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

Help with function created in VBA
 

You have another response at your first(?) post.

The difference is in the way negative numbers would be treated.

Bob K wrote:

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

--

Dave Peterson