Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum value after select case
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum value after select case
You didn't like the reply to your post on Jan 4?
Gimp wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum value after select case
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 wrote: You didn't like the reply to your post on Jan 4? Gimp wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum value after select case
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. Gimp wrote: 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 wrote: You didn't like the reply to your post on Jan 4? Gimp wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conversion of Numercials to Figure | Excel Worksheet Functions | |||
Displays the number in text. (One thousand two hundred thirty four | Excel Worksheet Functions | |||
how to use spellnumber formula | Excel Worksheet Functions | |||
Currency to Text | Excel Worksheet Functions | |||
How to .. | Excel Discussion (Misc queries) |