My code currently asks for the product code and the quantity of the product, and displays the total cost and the discount the consumer gets. However, I now need to use a For Loop that asks the "different types of products bought," and thus, place my current code in this For LOOP. That is, each time through the loop you should get and displayinformation about a particular product purchased. I have spent hours trying to insert my code in a For Loop to no avail. Any help would be great! thanks!
Code
Sub Product()
Dim ProductCode As String
Dim ErrorCheck As Boolean
Dim Cost As Double, MinQty As Double, Discount As Double
Dim MyRange As Range
Dim found As Variant
Dim QtyBought As Integer
Dim TotalCost As Double
Set MyRange = Worksheets("Data").Cells '<-- the range containing the data provided
Do '"main" outer loop
Do '"Product code input" inner loop
ProductCode = Application.InputBox("Enter the Product's code.", Type:=2) '<--| force string input
Loop While ProductCode = ""
found = Application.Match(ProductCode, MyRange.Columns(1), 0) '<-- try getting ow index of prodcut code in 1st column of "MyRange" range
If IsError(found) Then '<--| if no match found...
MsgBox "The value entered was not found!" & vbCrLf & vbCrLf & "Please, try again", vbCritical + vbOKOnly '<-- inform the user and loop again
Else '<--| otherwise
With MyRange(found, 1) '<-- reference the matching cell
Cost = .Offset(0, 1).Value '<--| store "Cost from cell 1 column to the right of the referenced one
MinQty = .Offset(0, 2).Value '<--| store "MinQty" from cell 2 columns to the right of the referenced one
Discount = .Offset(0, 3).Value '<--| store "Discount" from cell 3 columns to the right of the referenced one
End With
End If
Loop While IsError(found)
'Obtaining QtyBought Value
QtyBought = InputBox("Enter the QtyBought ordered.")
'Error checking
Do Until ErrorCheck = False
If IsNumeric(QtyBought) = False Then
ErrorCheck = True
MsgBox ("Not a valid entry.")
QtyBought = InputBox("Enter the QtyBought ordered.")
Else
ErrorCheck = False
End If
Loop
'finding out the cost of the prodcut ordered.
TotalCost = Selection.Value * QtyBought
Discount = Selection.Value * Discount
'Obtaining discount rate
If QtyBought > MinQty Then
MsgBox ("You purchased " & QtyBought & "units of product " & ProductCode & ".The total cost is " & Format(TotalCost, "$#,##0") & "Because you purchased at least " & MinQty & "units, you get a discount of " & Discount & "on each unit")
Else
MsgBox ("Sorry, You don't qualify for any discount")
End If
End Sub
Display More