See my other post "Select Case and If...Then Statements in UserForm not
Calculati", Rick Rothstein (MVP) and Bob Phillips have some good suggestions!

Thanks for you help!

"JLGWhiz" wrote:

Ryan, I don't see any glaring code errors. I would suggest that you step
through the code line by line and make sure that the values you are getting
for your variables are the values you expect. If you don't know how to do
that in the VBA editor, you could use message boxes as in the example below:

Height = tbxDimHft1 + (tbxDimHins1 / 12)
Width = tbxDimWft1 + (tbxDimWins1 / 12)
MsgBox "Height - " & Height & ", Width - " & Width

MaxL = WorksheetFunction.Max(Height, Width)
MinL = WorksheetFunction.Min(Height, Width)
MsgBox "MaxL - " & MaxL & ", MinL - " & MinL

This will show you what you are actually calculating against your price list.

"RyanH" wrote:

I add the 6inches to the MinL in the If...Then Statements. Any idea its not
calculating correctly?

"JLGWhiz" wrote:

Never mind, I see that it is not adding 1/2 to Width and Height.

"RyanH" wrote:

I'm not sure what is going on with my code but it is not calculating
properly. I am building a quoting system for plastic sign faces. I have a
UserForm where the user enters Height(feet & inches) and Width(feet & inches)
into textboxes. My code converts the Height & Width to inches. Then the
code determines which dimension is the largest. The user then chooses what
type of plastic the sign face is made out of and the code finds the part
number and returns the price for that particular size plastic. The code then
adds 6 inches to the smallest dimension and multiples that total by the
plastic price. For example, if I enter Height 4ft. - 0ins. & Width 3ft. -
0ins., and select "Clear .150 High Impact Modified Acrylic" from the combo
box, then click the Calculate Command Button I should get $32.76 instead I'm
getting $47.32 in my Label Caption. Note: Part Number PL509 = $9.36. Also
when I make a change to one of the Height textboxes ( ft. or ins.), then
click the Calculate button, nothing changes, why? Any help would greatly be
appreciated!!


Dim Height As Long, Width As Long, material As Long
Dim MaxL As Long, MinL As Long

Height = tbxDimHft1 + (tbxDimHins1 / 12)
Width = tbxDimWft1 + (tbxDimWins1 / 12)

MaxL = WorksheetFunction.Max(Height, Width)
MinL = WorksheetFunction.Min(Height, Width)

Sheets("Parts List").Activate

Select Case cboMaterial

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 W <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False)
If 4 < W <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False)

Case "White .150 Polycarbonate"
If W <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False)
If 4 < W <= 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

lblCalculatedPrice.Caption = material
lblCalculatedPrice.Caption = Format(lblCalculatedPrice.Caption, "$
#,###,###.00")
Sheets("Quote").Activate