There is a sales promotion for fruits, herbs and vegetables. Based on the discount rules below, use For loop, Select Case and If/Then/Else structure to create a sub procedure, named “Task3”, to insert discount in column D and format the items on sale. The discount rules are as follow:
• For each Fruits category:
• No discount for order quantity less than 5 cases.
• 10% discount if order quantity from 5 to 15 cases.
• 20% discount for more than 15 cases.
• For each Herbs category:
• No discount for order quantity less than 10 cases.
• 5% discount if order quantity from 10 to 15 cases.
• 10% discount for more than 15 cases.
• For each Vegetables category:
• Kale: 12% discount for 20 cases or more
• Others: 12% discount for 5 cases or more
• Three items are currently on 30% off sale, including Cauliflower, Guava, and Mango. None of the above discount rules apply to the items on sale.
Dim i As Integer
Dim FinalRow As Integer
Dim thisCategory As String, thisProduct As String
Dim thisQty As Integer
Dim Sale As Boolean
Dim discount As Single
With Sheets("sheet1") FinalRow = Cells(Rows.Count, 1).End(xlUp).Row Range("D1").Value = "Discount" ' use For loop to check for category, product and ' quantity row by row so as to determine the discount For i = 2 To FinalRow thisCategory = Cells(i, 1).Value thisProduct = Cells(i, 2).Value thisQty = Cells(i, 3).Value ' Use select case to determine if the product is on sale ' Use IF and/or Select Case to determine the discount ' base on Product category and discount rules If Sale Then discount = 0.3 Else If thisCategory = "Fruits" Then Select Case thisQty Case Is < 5 discount = 0 Case 5 To 15 discount = 0.1 Case Is > 15 discount = 0.2 End Select ElseIf thisCategory = "Herbs" Then ' use select case to determine ' discount of Herbs ElseIf thisCategory = "Vegetables" Then ' use if and/or select case to ' determine discount of Vegetables ' insert discount in column D ' use IF and .Resize property to highlight the entire ' record (from column A to D) if the product is on ' sale by changing background color to yellow Next i End With