Minimum value after select case

[Gimp]

I'm stumped. I have the following code that finds the first match of
textbox10 in Column A and returns values from cells corisponding
columns within that row. One value is a dollar amount. I'm using a
Select Case statement that first finds what column to get this dollar
value, columns 7 - 12.

All works fine but I want the 'search' to contine until the lowest of
this value is found and that value be returned to textbox10.

So if the value in textbox9 is AAA and there are 4 AAA's in column A,
what I would like to be returned are the valuses from the row were the
select case statement sets has the lowest ....make sense? So, if the
four dollar amounts are $3.25 in cell J400, $4.00 in J500, $2.60 in
J600 and $5.00 in J700. the return should be from row 'J' as it has
the lowest value. Of course if there is only one AAA in column A, then
that row's data is returned....

I'm been racking my brian trying to come up with the best approach.

Any ideas would be wonderful.

Thanks...


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 "This field can not be blank"
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 for textbox9 ==
'
========================================================================================================================
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)
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
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 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

 

[Dave Peterson]

You didn't like the reply to your post on Jan 4?

I'm stumped. I have the following code that finds the first match of
textbox10 in Column A and returns values from cells corisponding
columns within that row. One value is a dollar amount. I'm using a
Select Case statement that first finds what column to get this dollar
value, columns 7 - 12.

All works fine but I want the 'search' to contine until the lowest of
this value is found and that value be returned to textbox10.

So if the value in textbox9 is AAA and there are 4 AAA's in column A,
what I would like to be returned are the valuses from the row were the
select case statement sets has the lowest ....make sense? So, if the
four dollar amounts are $3.25 in cell J400, $4.00 in J500, $2.60 in
J600 and $5.00 in J700. the return should be from row 'J' as it has
the lowest value. Of course if there is only one AAA in column A, then
that row's data is returned....

I'm been racking my brian trying to come up with the best approach.

Any ideas would be wonderful.

Thanks...

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 "This field can not be blank"
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 for textbox9 ==
'
========================================================================================================================
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)
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
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 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

 

[Gimp]

Busted! HaHa....Actually I was working through your suggestion. I
think I get myself confused (more than likely due to my lack of VBA
code experience). You made a suggestion, which I kinda understood what
you were saying but I did not really know how to or where to
incorporate into the code I had already. The Case statement I have now
works well, finding the 'first match' and selecting the correct column
for the dollar value for textbox10 as well as the values for textbox11,
12, 13. Admittedly I get confused on where to put the IF statement
you suggested. Within the current Do Until Loop, out side it, does
what your suggesting replace the select case statement...etc..

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


Also, not sure why you had a IF for cells(irow,"B").value. The
combobox2 will hold an alpha character, LAX, JFK, etc, Textbox10 holds
a numeric value, such as 45 or 100 or 150...whatever. This number
represents a weight value, it does not necessary have to be formatted.
In your example you had the IF row H is < than myweight...The results
or search would not be limited to column H. The column headings for 7
- 12 are those numbers, -45, 45, 100, 300, 500 and 1000. The data in
these cells are dollar values. The Select Case will do its thing and
select the corresponding column that is the closes match, as noted by
the 0.01 to 44.999 etc...But rather the column that is selected via the
Select Case statement, the 'WGT' variable.

Again, Dave, by no means any disrespect for throwing my question back
out there. I peruse this goggle group often and see your comments and
suggestions and have successfully used some of them for problems I'm
working on. It seem however, this one really has me stumped and I was
trying to 'place' and edit your code into my code and I was getting
either no return to the textbox or some weird return like
$36,399,279.00. Thanks for you assistance Dave.

 

[Dave Peterson]

I thought you wanted to match one combobox to a value in a certain column and to
match one text box to a value in another column. Change the columns to match
your data.

Then use your select case to find the correct column--but keep track of the
minimum and use that to compare with any of the matches you've found.

After you're done looking through all the rows, myWeight will contain the
smallest number (or 99^99) and you can use that.