Prompt user for input

[Guest]

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

 

[Guest]

Hi Jerry -

You’ve 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

 

[Guest]

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