Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a variable to determine row selection | Excel Programming | |||
Using a formula to determine variable rows in Chart | Charts and Charting in Excel | |||
How to add user input to determine a variable in a macro | Excel Programming | |||
how to determine calculation setting? | Excel Discussion (Misc queries) | |||
Calculation to determine days between two dates | Excel Worksheet Functions |