Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
Hi,
In Excel 2000, I'm calculating prices for several different quantities. A customer will request a quote for a part number and want the price for 10, 100, 250 and 1000, for example. My code gets the correct answer, but I'm wondering if there isn't a better way to write it because the code has a bunch of duplicate lines. Thanks for your feedback and thanks to Dave Peterson for his help getting me started. Here's the code: Private Sub cmdCalc_Click() Dim sCoreAdapShell As String 'this combines the core part name, the adapter 'configuration and the shell size to create the 'lookup value to use in the vlookup formula. Dim res As Variant 'this will hold the results of the vlookup formula Dim sInp As String 'quantity entered in inputbox Dim dInp As Double 'this holds the quantity as a value not a string sCoreAdapShell = txtCore.text & txtAdap_Config.text & txtShell.text sInp = InputBox("Enter the quantity", "Quantity") dInp = sInp If dInp < 0 Then Select Case dInp Case 1 To 10 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 5, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 10 To 20 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 6, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 20 To 50 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 7, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 50 To 100 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 8, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 100 To 250 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 9, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 250 To 500 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 10, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 500 To 1000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 11, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 1000 To 2500 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 12, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 2500 To 5000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 13, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 5000 To 10000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 14, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case Else sInp = "too large" End Select End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
Untested, uncompiled. Watch for typos.
It looks like the input you're asking the user for is a quantity to order. I would think that those could be whole numbers only (1, 2, 3, ...). No fractions allowed. So I changed that doubles to longs. And since you're populating a textbox (I think), I bet you want a formatted string (like currency???). So I used Format() in that last portion of the code. Option Explicit Private Sub cmdCalc_Click() Dim sCoreAdapShell As String Dim res As Variant Dim lInp As Long Dim WhichCol As Long Dim myRng As Range Dim myMax As Long Dim myMin As Long myMin = 1 myMax = 10000 sCoreAdapShell = txtCore.Text & txtAdap_Config.Text & txtShell.Text lInp = CLng(Application.InputBox(Prompt:="Enter the quantity", _ Title:="Quantity", _ Type:=1)) If lInp < myMin _ Or lInp myMax Then 'msgbox "Not valid???" Exit Sub End If 'I like this as a variable, so it sticks out more and is easier to 'see/change. Set myRng = ThisWorkbook.Worksheets("tblPriceListCorePart") _ .Range("tblPriceListCore") WhichCol = 0 Select Case dInp Case Is < 10: WhichCol = 5 Case Is < 21: WhichCol = 6 Case Is < 51: WhichCol = 7 Case Is < 101: WhichCol = 8 Case Is < 251: WhichCol = 9 Case Is < 501: WhichCol = 10 Case Is < 1001: WhichCol = 11 Case Is < 2501: WhichCol = 12 Case Is < 5001: WhichCol = 13 Case Is < 10001: WhichCol = 14 End Select If WhichCol = 0 Then 'this shouldn't happen! MsgBox "Contact Dan, there's a design error!" Exit Sub End If res = Application.VLookup(sCoreAdapShell, myRng, WhichCol, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.Text = "not found!" Else Me.txtShellEntrySum.Value _ = Format((res * Me.txtCore_Multiplier.Value) _ + ((res * Me.txtCore_Multiplier.Value) _ * txtMarkup.Value) + (txtSetup.Value / lInp), "#,###.00") End If End Sub dan dungan wrote: Hi, In Excel 2000, I'm calculating prices for several different quantities. A customer will request a quote for a part number and want the price for 10, 100, 250 and 1000, for example. My code gets the correct answer, but I'm wondering if there isn't a better way to write it because the code has a bunch of duplicate lines. Thanks for your feedback and thanks to Dave Peterson for his help getting me started. Here's the code: Private Sub cmdCalc_Click() Dim sCoreAdapShell As String 'this combines the core part name, the adapter 'configuration and the shell size to create the 'lookup value to use in the vlookup formula. Dim res As Variant 'this will hold the results of the vlookup formula Dim sInp As String 'quantity entered in inputbox Dim dInp As Double 'this holds the quantity as a value not a string sCoreAdapShell = txtCore.text & txtAdap_Config.text & txtShell.text sInp = InputBox("Enter the quantity", "Quantity") dInp = sInp If dInp < 0 Then Select Case dInp Case 1 To 10 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 5, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 10 To 20 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 6, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 20 To 50 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 7, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 50 To 100 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 8, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 100 To 250 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 9, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 250 To 500 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 10, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 500 To 1000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 11, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 1000 To 2500 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 12, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 2500 To 5000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 13, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 5000 To 10000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 14, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case Else sInp = "too large" End Select End If End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
Wow. Thanks Dave.
I'm getting ready to go home, but I'll look at this first thing on Monday. Thanks again, Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
I am pretty sure that the following macro does what your macro does...
Private Sub cmdCalc_Click() Dim sCoreAdapShell As String 'this combines the core part name, the 'adapter configuration and the shell 'size to create the lookup value to use 'in the vlookup formula. Dim res As Variant 'this will hold the results of the 'VLookup Formula Dim sInp As String 'quantity entered in inputbox Dim dInp As Double 'this holds the quantity as a value not 'a string Dim dInpFactor As Long 'this holds the 3rd argument to VLookUp sCoreAdapShell = txtCore.Text & txtAdap_Config.Text & txtShell.Text sInp = InputBox("Enter the quantity", "Quantity") dInp = sInp If dInp 10000 Then sInp = "too large" ElseIf dInp 0 Then dInpFactor = 5 - (dInp 10) - (dInp 20) - (dInp 50) - _ (dInp 100) - (dInp 250) - (dInp 500) - _ (dInp 1000) - (dInp 2500) - (dInp 5000) res = Application.VLookup(sCoreAdapShell, ThisWorkbook.Worksheets( _ "tblPriceListCorePart").Range( _ "tblPriceListCore"), dInpFactor, False) If IsError(res) Then Me.txtShellEntrySum.Text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + _ ((res * Me.txtCore_Multiplier.Value) * _ txtMarkup.Value) + (txtSetup.Value / dInp) End If End If End Sub -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi, In Excel 2000, I'm calculating prices for several different quantities. A customer will request a quote for a part number and want the price for 10, 100, 250 and 1000, for example. My code gets the correct answer, but I'm wondering if there isn't a better way to write it because the code has a bunch of duplicate lines. Thanks for your feedback and thanks to Dave Peterson for his help getting me started. Here's the code: Private Sub cmdCalc_Click() Dim sCoreAdapShell As String 'this combines the core part name, the adapter 'configuration and the shell size to create the 'lookup value to use in the vlookup formula. Dim res As Variant 'this will hold the results of the vlookup formula Dim sInp As String 'quantity entered in inputbox Dim dInp As Double 'this holds the quantity as a value not a string sCoreAdapShell = txtCore.text & txtAdap_Config.text & txtShell.text sInp = InputBox("Enter the quantity", "Quantity") dInp = sInp If dInp < 0 Then Select Case dInp Case 1 To 10 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 5, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 10 To 20 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 6, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 20 To 50 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 7, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 50 To 100 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 8, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 100 To 250 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 9, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 250 To 500 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 10, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 500 To 1000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 11, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 1000 To 2500 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 12, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 2500 To 5000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 13, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case 5000 To 10000 res = Application.VLookup(sCoreAdapShell, _ ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"), _ 14, False) If IsError(res) Then 'like #n/a in excel Me.txtShellEntrySum.text = "not found!" Else Me.txtShellEntrySum.Value = (res * Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) * txtMarkup.Value) + (txtSetup.Value / dInp) End If Case Else sInp = "too large" End Select End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
Hi Dave,
Your code works great. I'm getting some errors when Me.txtShellEntrySum.Text = "not found!. In other code, I'm getting errors when I hide or show different userforms. I need to gather more facts to see what is going on. Thanks for your help. Dan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
Hi Rick,
Thank you, rick. Your code works, as well. I'm not sure which code to use. Is there some advantage to either approach? thanks again, Dan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
I doubt there is an advantage using either approach other than readability.
I tend to like more compact code, so I don't really like a long string of Case statements (although I will admit they are easier for most people to read)... my approach was to sum up the True/False results from the logical comparisons of the dInp variable against the various range limits for it. Just so you know, True in the VBA world equates to negative one (-1) and NOT plus one (+1) like it does in worksheet formulas... this is why I have minus signs in front of all my logical expressions... it turns the minus ones from the True logical expressions to plus values (minus a minus value is a plus value... it's like multiplying minus one times the minus value, which is also minus one in the case of True... minus one times zero is still zero). Anyway, if it is easier for you to see what is going on using the string of Case statements, then that may be the way you should go... your choice, of course. -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick, Thank you, rick. Your code works, as well. I'm not sure which code to use. Is there some advantage to either approach? thanks again, Dan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case simplification request
Thanks for your explanations, Rick
Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Select case or If then | Excel Programming | |||
End Select without Select Case, Block If without End If errors | Excel Programming | |||
VBA for the case when web request returns nothing. | Excel Programming |