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