Determine which variable was used in a calculation.
I have a Worksheet named "Parts", that has a list of parts in it:
Col. A - Unit Part Numbers Col. B - Unit Descriptions Col. C - Unit Type (ex.: sq. ft., ft., each, etc.) Col. D - Unit Cost I have a UserForm with two command buttons in it, cmbCalculate & cmbBOM. When cmbCalculate is clicked it calculates the quantity of each part, finds the associated part number, and multiplies the quantity and the Unit Cost to give me a total price. Example, Private Sub Calculate_Click () Dim Part1_Cost As Currency Dim Part2_Cost As Currency Dim Part3_Cost As Currency Dim Perimeter as Single Part1_Cost = WorksheetFunction.VLookup("Part-1", Sheets("Parts List").Range("A:D"), 4, False) Part2_Cost = WorksheetFunction.VLookup("Part-2", Sheets("Parts List").Range("A:D"), 4, False) Part3_Cost = WorksheetFunction.VLookup("Part-3", Sheets("Parts List").Range("A:D"), 4, False) Perimeter = (tbxHeight + tbxWidth) * 2 If chkAddTrimCap = True Then Select Case cboTrimCap Case "Black" TrimCapM = Perimeter * Val(tbxQuantity) * Part1_Cost Case "White" TrimCapM = Perimeter * Val(tbxQuantity) * Part2_Cost Case "Red" TrimCapM = Perimeter * Val(tbxQuantity) * Part3_Cost End Select End If End Sub I want to produce a bill of material for the product on a separate worksheet labeled "BOM" when cmbBOM is clicked. This BOM will need the following information of the part that was used: Col. A - Unit Part Numbers Col. B - Unit Descriptions Col. C - Unit Type (ex.: sq. ft., ft., each, etc.) Col. D - Unit Cost Col. E - Quantity Used Is it possible for VBA to know that TrimCapM used Part1_Cost or Part2_Cost or Part3_Cost? |
Determine which variable was used in a calculation.
You could just add another variable and keep track of what you wanted.
Dim WhichWasUsed as String ..... whichwasused = "" If chkAddTrimCap = True Then Select Case cboTrimCap Case "Black" TrimCapM = Perimeter * Val(tbxQuantity) * Part1_Cost whichwasused = "Part1" Case "White" TrimCapM = Perimeter * Val(tbxQuantity) * Part2_Cost Whichwasused = "Part2" Case "Red" TrimCapM = Perimeter * Val(tbxQuantity) * Part3_Cost whichwasused = "Part3" End Select End If if whichwasused = "" then msgbox "nothing used" else msgbox whichwasused end if .... RyanH wrote: I have a Worksheet named "Parts", that has a list of parts in it: Col. A - Unit Part Numbers Col. B - Unit Descriptions Col. C - Unit Type (ex.: sq. ft., ft., each, etc.) Col. D - Unit Cost I have a UserForm with two command buttons in it, cmbCalculate & cmbBOM. When cmbCalculate is clicked it calculates the quantity of each part, finds the associated part number, and multiplies the quantity and the Unit Cost to give me a total price. Example, Private Sub Calculate_Click () Dim Part1_Cost As Currency Dim Part2_Cost As Currency Dim Part3_Cost As Currency Dim Perimeter as Single Part1_Cost = WorksheetFunction.VLookup("Part-1", Sheets("Parts List").Range("A:D"), 4, False) Part2_Cost = WorksheetFunction.VLookup("Part-2", Sheets("Parts List").Range("A:D"), 4, False) Part3_Cost = WorksheetFunction.VLookup("Part-3", Sheets("Parts List").Range("A:D"), 4, False) Perimeter = (tbxHeight + tbxWidth) * 2 If chkAddTrimCap = True Then Select Case cboTrimCap Case "Black" TrimCapM = Perimeter * Val(tbxQuantity) * Part1_Cost Case "White" TrimCapM = Perimeter * Val(tbxQuantity) * Part2_Cost Case "Red" TrimCapM = Perimeter * Val(tbxQuantity) * Part3_Cost End Select End If End Sub I want to produce a bill of material for the product on a separate worksheet labeled "BOM" when cmbBOM is clicked. This BOM will need the following information of the part that was used: Col. A - Unit Part Numbers Col. B - Unit Descriptions Col. C - Unit Type (ex.: sq. ft., ft., each, etc.) Col. D - Unit Cost Col. E - Quantity Used Is it possible for VBA to know that TrimCapM used Part1_Cost or Part2_Cost or Part3_Cost? -- Dave Peterson |
Determine which variable was used in a calculation.
After thinking about it, I figured that is what I would need to do. I was
just wondering if there was some "magic" function or statement that I didn't know about. Thanks for the help! "Dave Peterson" wrote: You could just add another variable and keep track of what you wanted. Dim WhichWasUsed as String ..... whichwasused = "" If chkAddTrimCap = True Then Select Case cboTrimCap Case "Black" TrimCapM = Perimeter * Val(tbxQuantity) * Part1_Cost whichwasused = "Part1" Case "White" TrimCapM = Perimeter * Val(tbxQuantity) * Part2_Cost Whichwasused = "Part2" Case "Red" TrimCapM = Perimeter * Val(tbxQuantity) * Part3_Cost whichwasused = "Part3" End Select End If if whichwasused = "" then msgbox "nothing used" else msgbox whichwasused end if .... RyanH wrote: I have a Worksheet named "Parts", that has a list of parts in it: Col. A - Unit Part Numbers Col. B - Unit Descriptions Col. C - Unit Type (ex.: sq. ft., ft., each, etc.) Col. D - Unit Cost I have a UserForm with two command buttons in it, cmbCalculate & cmbBOM. When cmbCalculate is clicked it calculates the quantity of each part, finds the associated part number, and multiplies the quantity and the Unit Cost to give me a total price. Example, Private Sub Calculate_Click () Dim Part1_Cost As Currency Dim Part2_Cost As Currency Dim Part3_Cost As Currency Dim Perimeter as Single Part1_Cost = WorksheetFunction.VLookup("Part-1", Sheets("Parts List").Range("A:D"), 4, False) Part2_Cost = WorksheetFunction.VLookup("Part-2", Sheets("Parts List").Range("A:D"), 4, False) Part3_Cost = WorksheetFunction.VLookup("Part-3", Sheets("Parts List").Range("A:D"), 4, False) Perimeter = (tbxHeight + tbxWidth) * 2 If chkAddTrimCap = True Then Select Case cboTrimCap Case "Black" TrimCapM = Perimeter * Val(tbxQuantity) * Part1_Cost Case "White" TrimCapM = Perimeter * Val(tbxQuantity) * Part2_Cost Case "Red" TrimCapM = Perimeter * Val(tbxQuantity) * Part3_Cost End Select End If End Sub I want to produce a bill of material for the product on a separate worksheet labeled "BOM" when cmbBOM is clicked. This BOM will need the following information of the part that was used: Col. A - Unit Part Numbers Col. B - Unit Descriptions Col. C - Unit Type (ex.: sq. ft., ft., each, etc.) Col. D - Unit Cost Col. E - Quantity Used Is it possible for VBA to know that TrimCapM used Part1_Cost or Part2_Cost or Part3_Cost? -- Dave Peterson |
All times are GMT +1. The time now is 03:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com