| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textboxes not Calculating properly
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textboxes not Calculating properly
Height = tbxDimHft1 + (tbxDimHins1 / 12) '?add .5 ft ?
Width = tbxDimWft1 + (tbxDimWins1 / 12) If MaxL <= 4 Then material = (MinL + 0.5) '?add .5 ft? MinL = WorksheetFunction.Min(Height, Width) If these do what I think they do, then you are adding the extra half foot twice. Would that give you the $47.32? "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textboxes not Calculating properly
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textboxes not Calculating properly
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textboxes not Calculating properly
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textboxes not Calculating properly
I made some minor changes to the code to make it easier to read and I took
your advice and checked every step of the code. I found where the problem is, but do not know how to correct it and I hope maybe you could help. Going back to an example whe MaxL = 4 MinL = 3 cboMaterial = "Clear .150 High Impact Modified Acrylic" Note: "PL509" = $9.36 and "PL505" = $13.52 If I deactivate the 4 < MaxL <= 6 If...Then Statement, tbxCalculatedPrice shows the correct result ($33.00), because it uses the MaxL <= 4 If...Then Statement. If I activate both If...Then Statements under Case "Clear .150 High Impact Modified Acrylic", tbxCalculatedPrice shows the result of the 4 < MaxL <= 6 If...Then Statement which is incorrect ($47.00), Why? For some reason the code wants to use the 4 < MaxL <= 6 If.. Then Statement even though MaxL is equal to 4 not greater than four, Why? Private Sub cmbCalculate_Click() Dim Height As Double, Width As Double Dim MaxL As Long, MinL As Long Dim material As Long Height = Val(tbxDimHft1) + Val(tbxDimHins1) / 12 Width = Val(tbxDimWft1) + Val(tbxDimWins1) / 12 MaxL = WorksheetFunction.Max(Height, Width) MinL = WorksheetFunction.Min(Height, Width) Select Case cboMaterial Case "Clear .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL509", Sheets("Parts List").Range("A:D"), 4, False) ' If MaxL < 4 Or MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL505", Sheets("Parts List").Range("A:D"), 4, False) Case "Clear .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL522", Sheets("Parts List").Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL524", Sheets("Parts List").Range("A:D"), 4, False) Case "White .150 High Impact Modified Acrylic" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL510", Sheets("Parts List").Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL506", Sheets("Parts List").Range("A:D"), 4, False) Case "White .150 Polycarbonate" If MaxL <= 4 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL521", Sheets("Parts List").Range("A:D"), 4, False) If 4 < MaxL <= 6 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL529", Sheets("Parts List").Range("A:D"), 4, False) Case "Clear .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL503", Sheets("Parts List").Range("A:D"), 4, False) Case "White .177 High Impact Modified Acrylic" If MaxL <= 8 Then material = (MinL + 0.5) * WorksheetFunction.VLookup("PL504", Sheets("Parts List").Range("A:D"), 4, False) End Select tbxCalculatedPrice = material tbxCalculatedPrice = Format(tbxCalculatedPrice, "$ #,###,###.00") End Sub "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textboxes not Calculating properly
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Show Results in TextBoxes, ONLY if All TextBoxes have Data in them | Excel Programming | |||
| calculating textboxes | Excel Programming | |||
| Formulas not calculating properly | Excel Discussion (Misc queries) | |||
| CALCULATING WORKSHEETS (INCLUDING AGE CALCULATING SHEETS) FOR DOWNLOADING, GREAT FOR PENSIONS/LIFE INSURANCE CALCULATIONS! | Excel Programming | |||
| Calculating with textboxes.... | Excel Programming | |||
Hybrid Mode
