# (Solved) – Anyone know how to do Excel VBA 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
``````

End Sub