(Solved) – Anyone know how to do Excel VBA

(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.

Sub Task3()
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

How task3 should look like

Leave a Reply

Your email address will not be published. Required fields are marked *