VBA Help in 'Applying a Discount

[LRogers81]

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

 

[LRogers81]

Just a note: .Range("A44") is:

A4)Product code = C3972
Unit cost = $52
Minimum purchase quantity for discount = 20
D4)Discount = 7%

 

[Don Guillett]

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

 

[LRogers81]

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

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,

 

[Don Guillett]

Most TOP post in these groups. I don't think it would change it unless you
are applying to many values. Also, we don't usually do homework. Send the
textbook.