R
RyanH
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"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL505", Range("A"), 4, False)
Case "Clear .150 Polycarbonate"
If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL522", Range("A"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL524", Range("A"), 4, False)
Case "White .150 High Impact Modified Acrylic"
If W <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL510", Range("A"), 4, False)
If 4 < W <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL506", Range("A"), 4, False)
Case "White .150 Polycarbonate"
If W <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL521", Range("A"), 4, False)
If 4 < W <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL529", Range("A"), 4, False)
Case "Clear .177 High Impact Modified Acrylic"
If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL503", Range("A"), 4, False)
Case "White .177 High Impact Modified Acrylic"
If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL504", Range("A"), 4, False)
End Select
lblCalculatedPrice.Caption = material
lblCalculatedPrice.Caption = Format(lblCalculatedPrice.Caption, "$
#,###,###.00")
Sheets("Quote").Activate
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"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL505", Range("A"), 4, False)
Case "Clear .150 Polycarbonate"
If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL522", Range("A"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL524", Range("A"), 4, False)
Case "White .150 High Impact Modified Acrylic"
If W <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL510", Range("A"), 4, False)
If 4 < W <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL506", Range("A"), 4, False)
Case "White .150 Polycarbonate"
If W <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL521", Range("A"), 4, False)
If 4 < W <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL529", Range("A"), 4, False)
Case "Clear .177 High Impact Modified Acrylic"
If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL503", Range("A"), 4, False)
Case "White .177 High Impact Modified Acrylic"
If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL504", Range("A"), 4, False)
End Select
lblCalculatedPrice.Caption = material
lblCalculatedPrice.Caption = Format(lblCalculatedPrice.Caption, "$
#,###,###.00")
Sheets("Quote").Activate