Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help in 'Applying a Discount
With a lot of help, the code inputed below is for an excel file. The
crux of the situation is that the program has to check Range("A4:A131") to see if the product code entered is in the list and if it is then it asks for user input to see how many units the person purchased. If it is over a certain amount then it applies a discount, if not then no discount is applied. The following is what the cells look like. Problem 1) When I run the sub, however, it just loops right on through without breaking so I Ctrl-Break into it and get out of the program. Problem 2) I need to know how to apply the discount and put it in cell E4, is what I have under the If statement good enough or not (#1 it doesn't check itself against the Minimum purchase qty or unit cost, that could be a problem) Product code Unit cost Minimum purchase quantity for discount Discount C3972 $52 20 7% Sub PriceData2() Dim productRow As String Dim foundRowNum As Integer Dim i As Integer Dim numberPurchasedStr As String productRow = InputBox("Enter the product code") i = 4 Do Until i = 131 If Range("A" & i).Value = productRow Then foundRowNum = i MsgBox "The row for product " & productRow & " is " & foundRowNum & ".", _ vbInformation Exit Do End If Loop numberPurchasedStr = InputBox("Enter the number purchased") If (CInt(numberPurchasedStr) = Range("C" & foundRowNum).Value) Then With Range("E4") .Offset(numberPurchasedStr, 0) = numberPurchasedStr End With Range("E4") = Range("B14") ' Apply discount Else ' Do not apply discount End If End Sub Thanks, -Lee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help in 'Applying a Discount
Just a note: .Range("A4:D4") is:
A4)Product code = C3972 Unit cost = $52 Minimum purchase quantity for discount = 20 D4)Discount = 7% On May 10, 4:10 pm, wrote: With a lot of help, the code inputed below is for an excel file. The crux of the situation is that the program has to check Range("A4:A131") to see if the product code entered is in the list and if it is then it asks for user input to see how many units the person purchased. If it is over a certain amount then it applies a discount, if not then no discount is applied. The following is what the cells look like. Problem 1) When I run the sub, however, it just loops right on through without breaking so I Ctrl-Break into it and get out of the program. Problem 2) I need to know how to apply the discount and put it in cell E4, is what I have under the If statement good enough or not (#1 it doesn't check itself against the Minimum purchase qty or unit cost, that could be a problem) Product code Unit cost Minimum purchase quantity for discount Discount C3972 $52 20 7% Sub PriceData2() Dim productRow As String Dim foundRowNum As Integer Dim i As Integer Dim numberPurchasedStr As String productRow = InputBox("Enter the product code") i = 4 Do Until i = 131 If Range("A" & i).Value = productRow Then foundRowNum = i MsgBox "The row for product " & productRow & " is " & foundRowNum & ".", _ vbInformation Exit Do End If Loop numberPurchasedStr = InputBox("Enter the number purchased") If (CInt(numberPurchasedStr) = Range("C" & foundRowNum).Value) Then With Range("E4") .Offset(numberPurchasedStr, 0) = numberPurchasedStr End With Range("E4") = Range("B14") ' Apply discount Else ' Do not apply discount End If End Sub Thanks, -Lee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help in 'Applying a Discount
On May 10, 5:31 pm, "Don Guillett" wrote:
try this idea Sub getprice() Dim amt As Integer pc = InputBox("enter product code") amt = InputBox("enter amt purchased") cpu = Columns("g").Find(pc).Address gross = Range(cpu).Offset(, 1) * amt discount = Range(cpu).Offset(, 3) If amt < Range(cpu).Offset(, 2) Then sp = gross Else sp = gross * (1 - discount) End If MsgBox Application.Dollar(sp) End Sub -- Don Guillett SalesAid Software wrote in message ups.com... Just a note: .Range("A4:D4") is: A4)Product code = C3972 Unit cost = $52 Minimum purchase quantity for discount = 20 D4)Discount = 7% On May 10, 4:10 pm, wrote: With a lot of help, the code inputed below is for an excel file. The crux of the situation is that the program has to check Range("A4:A131") to see if the product code entered is in the list and if it is then it asks for user input to see how many units the person purchased. If it is over a certain amount then it applies a discount, if not then no discount is applied. The following is what the cells look like. Problem 1) When I run the sub, however, it just loops right on through without breaking so I Ctrl-Break into it and get out of the program. Problem 2) I need to know how to apply the discount and put it in cell E4, is what I have under the If statement good enough or not (#1 it doesn't check itself against the Minimum purchase qty or unit cost, that could be a problem) Product code Unit cost Minimum purchase quantity for discount Discount C3972 $52 20 7% Sub PriceData2() Dim productRow As String Dim foundRowNum As Integer Dim i As Integer Dim numberPurchasedStr As String productRow = InputBox("Enter the product code") i = 4 Do Until i = 131 If Range("A" & i).Value = productRow Then foundRowNum = i MsgBox "The row for product " & productRow & " is " & foundRowNum & ".", _ vbInformation Exit Do End If Loop numberPurchasedStr = InputBox("Enter the number purchased") If (CInt(numberPurchasedStr) = Range("C" & foundRowNum).Value) Then With Range("E4") .Offset(numberPurchasedStr, 0) = numberPurchasedStr End With Range("E4") = Range("B14") ' Apply discount Else ' Do not apply discount End If End Sub Thanks, -Lee Hey thanks...however, I need the Do Loop in order to do the problem correctly, this is a problem from a text book. Don't know if this changes what you posted but yeah, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help in 'Applying a Discount
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get qty discount | Excel Worksheet Functions | |||
how to do formula with a fix discount | Excel Worksheet Functions | |||
Applying a discount to a column of prices! | Excel Discussion (Misc queries) | |||
Discount calculation | Excel Worksheet Functions | |||
formula discount | New Users to Excel |