Prompt user for input
How do I write a macro to prompt a user for a margin percent number and then
take that number and multiple the actual product cost by that percent markup? Is there a built-in function for this process already in Excel 2000? Thanks |
Prompt user for input
Hi Jerry -
Youve used margin and markup in the same question. Note that these values are defined (and calculated) differently so two procedures are included below. Margin: The percentage margin is the percentage of the final selling price that is profit. Markup: A markup is the percentage of the cost price to add on to get the selling price. Both procedures assume that your ProductCost is in cell A2. Both calculate the final price (cost + margin or markup): Sub Margin() 'Margin Version: Final Price = Cost/(1-Margin/100) mPercent = InputBox("Please enter Margin Percent (whole number):") Range("B2") = Range("A2") / (1 - (mPercent / 100)) End Sub Sub Markup() 'Markup Version: FinalPrice = Cost * (1+Markup/100) mPercent = InputBox("Please enter Markup Percent (whole number):") Range("B2") = Range("A2") * (1 + (mPercent / 100)) End Sub -- Jay "Jerry Foley" wrote: How do I write a macro to prompt a user for a margin percent number and then take that number and multiple the actual product cost by that percent markup? Is there a built-in function for this process already in Excel 2000? Thanks |
Prompt user for input
Here is a similar method
Sub ProdMarg() marginPct = Application.InputBox("Enter the Margin Percentage as a whole number. No Decimal point.", "Margin", Type:=1) marginPct = marginPct/100 ProdCost = Application.InputBox("Enter Product Cost as whole number", "Product Cost", Type:=1) ProdCost = (ProdCost * marginPct) + ProdCost MsgBox "$" & ProdCost End Sub "Jerry Foley" wrote: How do I write a macro to prompt a user for a margin percent number and then take that number and multiple the actual product cost by that percent markup? Is there a built-in function for this process already in Excel 2000? Thanks |
All times are GMT +1. The time now is 08:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com