| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case and If...Then Statements in UserForm not Calculating
I am building a product quoting system in VBA for my company that
manufactures outdoor plastic sign faces. I have a Userform with 6 text boxes, 1 combobox, command button: tbxDimHft1 = user enters Height of sign in feet tbxDimHins1 = user enters Height of sign in inches tbxDimWft1 = user enters Width of sign in feet tbxDimWins1 = user enters Width of sign in inches tbxCalculatedPrice = shows calculated price when command button is clicked tbxQuotePrice = shows quote price when command button is clicked cboMaterial = contains material for signs cmbCalculate = command button to run calculation code In my current code tbxCalculatedPrice and tbxQuotePrice should equal the same, but for some reason they are not equalling each other when I click the Calculate Button. For example, if I enter the following data: tbxDimHft1 = 4 tbxDimHins1 = 0 tbxDimWft1 = 3 tbxDimWins1 = 0 cboMaterial = "Clear .150 High Impact Modified Acrylic" Click the command button tbxQuotePrice = 32.76 tbxCalculatePrice = 47.00 WHY???? Any help would be greatly appreciated, because I have looked at this forever and cannot see why the calculations are not the same. THANKS!! Note: PL509 = $9.36 Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified Acrylic" Then tbxQuotePrice = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) Sheets("Quote").Activate End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case and If...Then Statements in UserForm not Calculating
text boxes contain text and won't comparre to number values on the worksheet.
You havve to convert the text bak to a number using the Val() function. I usually add a trim to remove spaces. numval = trim(val((texttbox_data)) "RyanH" wrote: I am building a product quoting system in VBA for my company that manufactures outdoor plastic sign faces. I have a Userform with 6 text boxes, 1 combobox, command button: tbxDimHft1 = user enters Height of sign in feet tbxDimHins1 = user enters Height of sign in inches tbxDimWft1 = user enters Width of sign in feet tbxDimWins1 = user enters Width of sign in inches tbxCalculatedPrice = shows calculated price when command button is clicked tbxQuotePrice = shows quote price when command button is clicked cboMaterial = contains material for signs cmbCalculate = command button to run calculation code In my current code tbxCalculatedPrice and tbxQuotePrice should equal the same, but for some reason they are not equalling each other when I click the Calculate Button. For example, if I enter the following data: tbxDimHft1 = 4 tbxDimHins1 = 0 tbxDimWft1 = 3 tbxDimWins1 = 0 cboMaterial = "Clear .150 High Impact Modified Acrylic" Click the command button tbxQuotePrice = 32.76 tbxCalculatePrice = 47.00 WHY???? Any help would be greatly appreciated, because I have looked at this forever and cannot see why the calculations are not the same. THANKS!! Note: PL509 = $9.36 Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified Acrylic" Then tbxQuotePrice = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) Sheets("Quote").Activate End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case and If...Then Statements in UserForm not Calculati
I tried this and I am getting the same result. There are only numbers
entered into the textboxes anyway. Is the Trim function redundant? I thought Val() returns all numbers to the left of anything that is not a number except a decimal point. Should I use a label to show the results instead? Not sure if there is a better way. The weird thing is if you look at my example posted earlier, under the '''''''''''''''''''quote price textbox value'''''''''''''' has the same parameters as '''''''''''''calculated price text box''''''''''''''''', just coded slightly different. Can you not embbed If...Then statements into Select Case? The result in tbxQuotePrice is correct ($32.76), but tbxCalculatedPrice is Incorrect ($47.00), why? Forgive me if I do not understand, I am still learning, lol. "Joel" wrote: text boxes contain text and won't comparre to number values on the worksheet. You havve to convert the text bak to a number using the Val() function. I usually add a trim to remove spaces. numval = trim(val((texttbox_data)) "RyanH" wrote: I am building a product quoting system in VBA for my company that manufactures outdoor plastic sign faces. I have a Userform with 6 text boxes, 1 combobox, command button: tbxDimHft1 = user enters Height of sign in feet tbxDimHins1 = user enters Height of sign in inches tbxDimWft1 = user enters Width of sign in feet tbxDimWins1 = user enters Width of sign in inches tbxCalculatedPrice = shows calculated price when command button is clicked tbxQuotePrice = shows quote price when command button is clicked cboMaterial = contains material for signs cmbCalculate = command button to run calculation code In my current code tbxCalculatedPrice and tbxQuotePrice should equal the same, but for some reason they are not equalling each other when I click the Calculate Button. For example, if I enter the following data: tbxDimHft1 = 4 tbxDimHins1 = 0 tbxDimWft1 = 3 tbxDimWins1 = 0 cboMaterial = "Clear .150 High Impact Modified Acrylic" Click the command button tbxQuotePrice = 32.76 tbxCalculatePrice = 47.00 WHY???? Any help would be greatly appreciated, because I have looked at this forever and cannot see why the calculations are not the same. THANKS!! Note: PL509 = $9.36 Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified Acrylic" Then tbxQuotePrice = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) Sheets("Quote").Activate End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case and If...Then Statements in UserForm not Calculating
Your problem is with the second If-Then test you are performing in the Case
block for your material... If 4 < MaxL <= 6 Then .... The problem with the above test is that it is written incorrectly... it will evaluate **always** evaluate to True. The two operators you are using have the same precedence, so they are evaluated left-to-right. No matter what value is assigned to MaxL, 4<MaxL will be either True or False which, in the VB world, is either -1 or 0, both of which are always going to be less than 6; hence, the above test will always be True and **always** overwrite any value assigned to the materials variable by the previous statement. Since you don't perform this same second test when making your assignment to tbxQuotePrice near the end of your code, the values you think should be the same will always be different when MaxL is equal to, or less than, 4 (at least for the code snippet you have shown us). Anyway, the proper way to write the above If-Then test is this... If 4 < MaxL And MaxL <=6 Then ... Now, there may be another problem with your code. You have declared each of your variables as type Long, but some of your calculations will result in floating point value being assigned to them. When that assignment takes place, the fractional values will be lost and, I presume, cause your calculations to be performed with values other than what you intended. For example, this assignment... Height = tbxDimHft1 + (tbxDimHins1 / 12) will result in a whole number, not a floating point value, being assigned to the Height variable. The same is true for the Width variable and any subsequent calculations derived from them. Your materials variable, also declared as Long is another place where any fractional values generated as part of its assignment calculation will be lost. You might want to consider declaring these (and perhaps other) variables as Double instead. Rick "RyanH" wrote in message ... I am building a product quoting system in VBA for my company that manufactures outdoor plastic sign faces. I have a Userform with 6 text boxes, 1 combobox, command button: tbxDimHft1 = user enters Height of sign in feet tbxDimHins1 = user enters Height of sign in inches tbxDimWft1 = user enters Width of sign in feet tbxDimWins1 = user enters Width of sign in inches tbxCalculatedPrice = shows calculated price when command button is clicked tbxQuotePrice = shows quote price when command button is clicked cboMaterial = contains material for signs cmbCalculate = command button to run calculation code In my current code tbxCalculatedPrice and tbxQuotePrice should equal the same, but for some reason they are not equalling each other when I click the Calculate Button. For example, if I enter the following data: tbxDimHft1 = 4 tbxDimHins1 = 0 tbxDimWft1 = 3 tbxDimWins1 = 0 cboMaterial = "Clear .150 High Impact Modified Acrylic" Click the command button tbxQuotePrice = 32.76 tbxCalculatePrice = 47.00 WHY???? Any help would be greatly appreciated, because I have looked at this forever and cannot see why the calculations are not the same. THANKS!! Note: PL509 = $9.36 Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified Acrylic" Then tbxQuotePrice = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) Sheets("Quote").Activate End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case and If...Then Statements in UserForm not Calculati
IN WORKS!!!!!!!
I spent many hours of a day trying to solve this issue. Thanks so much, you are the man! I have another posting about this so I will have to let them know what solution is and of course give you credit! Thanks Again! "Rick Rothstein (MVP - VB)" wrote: Your problem is with the second If-Then test you are performing in the Case block for your material... If 4 < MaxL <= 6 Then .... The problem with the above test is that it is written incorrectly... it will evaluate **always** evaluate to True. The two operators you are using have the same precedence, so they are evaluated left-to-right. No matter what value is assigned to MaxL, 4<MaxL will be either True or False which, in the VB world, is either -1 or 0, both of which are always going to be less than 6; hence, the above test will always be True and **always** overwrite any value assigned to the materials variable by the previous statement. Since you don't perform this same second test when making your assignment to tbxQuotePrice near the end of your code, the values you think should be the same will always be different when MaxL is equal to, or less than, 4 (at least for the code snippet you have shown us). Anyway, the proper way to write the above If-Then test is this... If 4 < MaxL And MaxL <=6 Then ... Now, there may be another problem with your code. You have declared each of your variables as type Long, but some of your calculations will result in floating point value being assigned to them. When that assignment takes place, the fractional values will be lost and, I presume, cause your calculations to be performed with values other than what you intended. For example, this assignment... Height = tbxDimHft1 + (tbxDimHins1 / 12) will result in a whole number, not a floating point value, being assigned to the Height variable. The same is true for the Width variable and any subsequent calculations derived from them. Your materials variable, also declared as Long is another place where any fractional values generated as part of its assignment calculation will be lost. You might want to consider declaring these (and perhaps other) variables as Double instead. Rick "RyanH" wrote in message ... I am building a product quoting system in VBA for my company that manufactures outdoor plastic sign faces. I have a Userform with 6 text boxes, 1 combobox, command button: tbxDimHft1 = user enters Height of sign in feet tbxDimHins1 = user enters Height of sign in inches tbxDimWft1 = user enters Width of sign in feet tbxDimWins1 = user enters Width of sign in inches tbxCalculatedPrice = shows calculated price when command button is clicked tbxQuotePrice = shows quote price when command button is clicked cboMaterial = contains material for signs cmbCalculate = command button to run calculation code In my current code tbxCalculatedPrice and tbxQuotePrice should equal the same, but for some reason they are not equalling each other when I click the Calculate Button. For example, if I enter the following data: tbxDimHft1 = 4 tbxDimHins1 = 0 tbxDimWft1 = 3 tbxDimWins1 = 0 cboMaterial = "Clear .150 High Impact Modified Acrylic" Click the command button tbxQuotePrice = 32.76 tbxCalculatePrice = 47.00 WHY???? Any help would be greatly appreciated, because I have looked at this forever and cannot see why the calculations are not the same. THANKS!! Note: PL509 = $9.36 Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified Acrylic" Then tbxQuotePrice = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) Sheets("Quote").Activate End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case and If...Then Statements in UserForm not Calculating
Not tested , but tere were syntax errors in the code
Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long With WorksheetFunction Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = .Max(Height, Width) MinL = .Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL509", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL522", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL510", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL521", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * _ .VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * _ .VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = _ "Clear .150 High Impact Modified Acrylic " Then tbxQuotePrice = (MinL + 0.5) * _ .VLookup("PL509", Range("A:D"), 4, False) End If Sheets("Quote").Activate End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyanH" wrote in message ... I am building a product quoting system in VBA for my company that manufactures outdoor plastic sign faces. I have a Userform with 6 text boxes, 1 combobox, command button: tbxDimHft1 = user enters Height of sign in feet tbxDimHins1 = user enters Height of sign in inches tbxDimWft1 = user enters Width of sign in feet tbxDimWins1 = user enters Width of sign in inches tbxCalculatedPrice = shows calculated price when command button is clicked tbxQuotePrice = shows quote price when command button is clicked cboMaterial = contains material for signs cmbCalculate = command button to run calculation code In my current code tbxCalculatedPrice and tbxQuotePrice should equal the same, but for some reason they are not equalling each other when I click the Calculate Button. For example, if I enter the following data: tbxDimHft1 = 4 tbxDimHins1 = 0 tbxDimWft1 = 3 tbxDimWins1 = 0 cboMaterial = "Clear .150 High Impact Modified Acrylic" Click the command button tbxQuotePrice = 32.76 tbxCalculatePrice = 47.00 WHY???? Any help would be greatly appreciated, because I have looked at this forever and cannot see why the calculations are not the same. THANKS!! Note: PL509 = $9.36 Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified Acrylic" Then tbxQuotePrice = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) Sheets("Quote").Activate End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case and If...Then Statements in UserForm not Calculati
I knew it had something to do with that 4 < MaxL <= 6, but didn't know how to
fix it. Plus Rick Rothstein (MVP) suggested to declare my variables as Double instead of Long so I could incorporate my decimal places. Thanks for your help! "Bob Phillips" wrote: Not tested , but tere were syntax errors in the code Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long With WorksheetFunction Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = .Max(Height, Width) MinL = .Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL509", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL522", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL510", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * _ .VLookup("PL521", Range("A:D"), 4, False) ElseIf MaxL <= 6 Then material = (MinL + 0.5) * _ .VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * _ .VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * _ .VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = _ "Clear .150 High Impact Modified Acrylic " Then tbxQuotePrice = (MinL + 0.5) * _ .VLookup("PL509", Range("A:D"), 4, False) End If Sheets("Quote").Activate End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyanH" wrote in message ... I am building a product quoting system in VBA for my company that manufactures outdoor plastic sign faces. I have a Userform with 6 text boxes, 1 combobox, command button: tbxDimHft1 = user enters Height of sign in feet tbxDimHins1 = user enters Height of sign in inches tbxDimWft1 = user enters Width of sign in feet tbxDimWins1 = user enters Width of sign in inches tbxCalculatedPrice = shows calculated price when command button is clicked tbxQuotePrice = shows quote price when command button is clicked cboMaterial = contains material for signs cmbCalculate = command button to run calculation code In my current code tbxCalculatedPrice and tbxQuotePrice should equal the same, but for some reason they are not equalling each other when I click the Calculate Button. For example, if I enter the following data: tbxDimHft1 = 4 tbxDimHins1 = 0 tbxDimWft1 = 3 tbxDimWins1 = 0 cboMaterial = "Clear .150 High Impact Modified Acrylic" Click the command button tbxQuotePrice = 32.76 tbxCalculatePrice = 47.00 WHY???? Any help would be greatly appreciated, because I have looked at this forever and cannot see why the calculations are not the same. THANKS!! Note: PL509 = $9.36 Private Sub cmbCalculate_Click() Dim Height As Long, Width As Long Dim MaxL As Long, MinL As Long Dim material As Long Height = tbxDimHft1 + (tbxDimHins1 / 12) Width = tbxDimWft1 + (tbxDimWins1 / 12) MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Sheets("Parts List").Activate ''''''''''''calculated price text box'''''''''''''''''''''''' Select Case cboMaterial.Text Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False) End Select tbxCalculatedPrice = material '''''''''''''quote price textbox value'''''''''''''''''''' If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified Acrylic" Then tbxQuotePrice = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False) Sheets("Quote").Activate End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statements to Select Case??? | Excel Discussion (Misc queries) | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| Select Case Statements | Excel Programming | |||
| Using Select Case Statements | Excel Programming | |||
Hybrid Mode
