Dave Peterson
12/15/2006 9:55:00 PM
Oops. I didn't notice that 0's should be excluded.
I think just changing this portion should work:
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
Bob K wrote:
>
> Dave
>
> The function seems to work for text and error but it includes an agruement
> whose value is zero. Any ideas?
>
> thanks,
>
> bob
>
> "Dave Peterson" wrote:
>
> > This seemed to work ok:
> >
> > Option Explicit
> > Function MinPrice(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
> > MinPrice = myMin
> > Else
> > MinPrice = "Item Not Bid"
> > End If
> > End Function
> > Sub aa()
> > MsgBox MinPrice("a", 3, CVErr(xlErrNA))
> > End Sub
> >
> > But maybe you could use an array formula:
> >
> > =IF(COUNT(A1:A10)=0,"Item Not Bid",MIN(IF(ISNUMBER(A1:A10),A1:A10)))
> >
> > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > them yourself.)
> >
> >
> > Bob K wrote:
> > >
> > > I am trying to create a function that looks at each argument and does
> > > validation of the argument to determine whether or not to include the
> > > argument in the min function. The validation process should exclude
> > > arguments that:
> > >
> > > Argument = 0
> > > Argument is text
> > > Argument is an error
> > >
> > > If the all the arguments fail the validation, it should return a text
> > > message ie ââ?¬Å?Item not bidââ?¬Â otherwise it should return the result of the min
> > > value of the arguments that pass the validation process. Any help would be
> > > much appreciated. I also want to create a function the works similar using
> > > ââ?¬Å?Smallââ?¬Â.
> > >
> > > Thanks,
> > >
> > > bob
> > >
> > > Function MinPrice(ParamArray arglist() As Variant) As Double
> > > For Each arg In arglist
> > > MinPrice = WorksheetFunction.Min(arglist)
> > > Next arg
> > > End Function
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson