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
|