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

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

Hi Dave

I've been working on what I think is something similar but running into
a wall. From looking at your last example code I'm thinking it might
work for me.

I have user form that has (for example) a combobox and two textboxes 9
and 10. The user would select from the drop down box (combobox) a
string, say ABC. Then they enter in a weight value in one textbox10,
say 45. When the button is hit, the code searches for ABC in Column A
and returns to textbox9 the value found in the same row of ABC what is
in Column H, say the dollar value of $3.25. This works just fine. But
I need the search to continue for the lowest dollar value in column H.
Say row 564 (H564) is = $3.25, but row 600 (H600) is = $2.50. What I
would like is the $2.50 to be returned to textbox9.

Does that make sense?

Thanks for any help, sorry I'm a newer VBA / Userform user....(by the
way, the wall is starting to leave marks!!!)


Here is my code thus far:

Private Sub CommandButton5_Click()
Dim myRw As Integer
Dim Gtwy As Integer
Dim Wgt As Variant

Gtwy = Sheets("AirlineData").Cells(Rows.Count, "A").End(xlUp).Row
myRw = 1

If TextBox10 = "" Then
MsgBox "~~~~~~~~~~~Go Bears~~~~~~~~~~~~"
Exit Sub
End If

Do Until myRw = Gtwy
'
================================================== ================================================== =====
' == Taking value from textbox10 the weight and using case to get
correct column for correct dollar amount ==
'
================================================== ================================================== =====
Wgt = TextBox10.Value

With Wgt
Select Case Wgt
Case 0.01 To 44.9999
Wgt = Cells(myRw, 7)
Case 45 To 55.9999
Wgt = Cells(myRw, 8) ' this would be column 'H'
as noted in my example
Case 56 To 149.9999
Wgt = Cells(myRw, 9)
Case 150 To 399.9999
Wgt = Cells(myRw, 10)
Case 400 To 749.9999
Wgt = Cells(myRw, 11)
Case 750 To 9999999.9999
Wgt = Cells(myRw, 12)
End Select
End With

If ComboBox2.Value = Cells(myRw, 1) Then
TextBox9.Value = Wgt ' Value based on weight - column H in this
example
TextBox11.Value = Cells(myRw, 2) ' GateWay column B
TextBox12.Value = Cells(myRw, 3) ' City column C
TextBox13.Value = Cells(myRw, 5) ' Airline column E
Exit Do
End If
myRw = myRw + 1
Loop

' Set Value of textbox 9 and 14 to Currency
TextBox9.Text = Format(TextBox9.Text, "currency") ' Value based on
weight column F to L
TextBox14.Value = TextBox9.Value * TextBox10.Value
TextBox14.Text = Format(TextBox14.Text, "currency")

End Sub

Thanks again for any help with this one...


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

Without looking to closely at your code, I think that if you just kept track of
the lowest value and used that, it would work ok.

Kind of like this:

dim myWeight as double
dim iRow as long
myweight = 99^99 'pretty big number
for irow = something to somethingelse
'change the columns to match your data
if me.combobox1.value = cells(irow,"A").value then
if me.textbox9.value = cells(irow,"B").value then
if cells(irow,"H").value < myweight then
myweight = cells(irow,"H").value
end if
end if
end if
next irow

if myweight = 99^99 then
'what should go in that textbox
me.textbox10.value = "No matches!
else
me.textbox10.value = format(myweight,"#,##0.00") 'if you want to format it
end if



Gimp wrote:

Hi Dave

I've been working on what I think is something similar but running into
a wall. From looking at your last example code I'm thinking it might
work for me.

I have user form that has (for example) a combobox and two textboxes 9
and 10. The user would select from the drop down box (combobox) a
string, say ABC. Then they enter in a weight value in one textbox10,
say 45. When the button is hit, the code searches for ABC in Column A
and returns to textbox9 the value found in the same row of ABC what is
in Column H, say the dollar value of $3.25. This works just fine. But
I need the search to continue for the lowest dollar value in column H.
Say row 564 (H564) is = $3.25, but row 600 (H600) is = $2.50. What I
would like is the $2.50 to be returned to textbox9.

Does that make sense?

Thanks for any help, sorry I'm a newer VBA / Userform user....(by the
way, the wall is starting to leave marks!!!)

Here is my code thus far:

Private Sub CommandButton5_Click()
Dim myRw As Integer
Dim Gtwy As Integer
Dim Wgt As Variant

Gtwy = Sheets("AirlineData").Cells(Rows.Count, "A").End(xlUp).Row
myRw = 1

If TextBox10 = "" Then
MsgBox "~~~~~~~~~~~Go Bears~~~~~~~~~~~~"
Exit Sub
End If

Do Until myRw = Gtwy
'
================================================== ================================================== =====
' == Taking value from textbox10 the weight and using case to get
correct column for correct dollar amount ==
'
================================================== ================================================== =====
Wgt = TextBox10.Value

With Wgt
Select Case Wgt
Case 0.01 To 44.9999
Wgt = Cells(myRw, 7)
Case 45 To 55.9999
Wgt = Cells(myRw, 8) ' this would be column 'H'
as noted in my example
Case 56 To 149.9999
Wgt = Cells(myRw, 9)
Case 150 To 399.9999
Wgt = Cells(myRw, 10)
Case 400 To 749.9999
Wgt = Cells(myRw, 11)
Case 750 To 9999999.9999
Wgt = Cells(myRw, 12)
End Select
End With

If ComboBox2.Value = Cells(myRw, 1) Then
TextBox9.Value = Wgt ' Value based on weight - column H in this
example
TextBox11.Value = Cells(myRw, 2) ' GateWay column B
TextBox12.Value = Cells(myRw, 3) ' City column C
TextBox13.Value = Cells(myRw, 5) ' Airline column E
Exit Do
End If
myRw = myRw + 1
Loop

' Set Value of textbox 9 and 14 to Currency
TextBox9.Text = Format(TextBox9.Text, "currency") ' Value based on
weight column F to L
TextBox14.Value = TextBox9.Value * TextBox10.Value
TextBox14.Text = Format(TextBox14.Text, "currency")

End Sub

Thanks again for any help with this one...

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