(Solved) – VBA with multiple discounts & Problem of next without for & insert value in respective cell

(solved)-–-vba-with-multiple-discounts-&-problem-of-next-without-for-&-insert-value-in-respective-cell

The problem here is we’ve got a promotion for fruits, herbs and vegetables. A vba code is needed under several discounts rules.
• Cauliflower, Guava and mango are on sale, with 30% off discount, will not be affected by the order quantity
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.
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.
Vegetables category:
• Kale: 12% discount for 20 cases or more
• Others: 12% discount for 5 cases or more

However, I am facing the issue of “next without for”……and I could not see where is the lack…and secondly may I know how to insert the respective discount in column D (under percentage). Any suggestion and help are more than appreciated. Thx !!

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"

    For i = 2 To FinalRow
        thisCategory = Cells(i, 1).Value
        thisProduct = Cells(i, 2).Value
        thisQty = Cells(i, 3).Value

        Select Case thisProduct
            Case "Cauliflower"
                Sale = thisProduct
            Case "Guava"
                Sale = thisProduct
            Case "Mango"
                Sale = thisProduct
        End Select

        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
                Select Case thisQty
                    Case Is < 10
                        discount = 0
                    Case 10 To 15
                        discount = 0.05
                    Case Is > 15
                        discount = 0.1
                End Select

            ElseIf thisCategory = "Vegetables" Then
                    If thisProduct = "Kale" And thisQty >= 20 Then
                        discount = 0.12
                    ElseIf thisProduct <> "Kale" And thisQty >= 5 Then
                        discount = 0.12
                    End If
        End If
        ' insert discount in column D


         If discount = 0.3 Then
            .Range(.Cells(i, 1), Cells(i, 4)).Interior.Color = vbYellow
         End If
    Next i
    End With

End Sub

Leave a Reply

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