VBA Select Case

Basic Exercise

A hat store intents to make a Black Friday sales promotion. However, before lowering the prices, the store manager decides to adjust the prices up so the given discounts will not reduce the store's margin of sales by much. To do that, he decided that he would follow the following rules:

Initial Price (US$)	Adjustment (percentage)
6 to 15	15%
15,01 to 25	25%
25,01 or more	35%

After these adjustments are applied, he decided that the Black Friday's discounts should be:

Adjusted Price (US$)	Discount (percentage)
6 to 15	10%
15,01 to 22	17.5%
22,01 to 33	25%
33,01 or more	30%

The hat store manager has given you the task to calculate the final discount price of the products and to compare it with their original values. The items that actually had their prices lowered by the store's "discount" should be colored green.

Suggested Concepts:

Dim - Declares a variable.
For Next - Repeats a group of statements a specified number of times.
If Else - Conditionally executes a group of statements, depending on the value of an expression.
Select Case - Runs one of several groups of statements, depending on the value of an expression.
End with .Row - Find the last row with data.
Cells - Allows VBA to interact with cells in the worksheet.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Make one Select Case statement for an increased final price and another one for a price with an actual discount both within the same For Next loop

Solved Exercise

Exercise Solution

Initially, we apply to each product the first price adjustment (price increment) in accordance to the rules stated by the exercise

We start by declaring the variables and finding the last data entry in the worksheet

Dim LastRow As Long
Dim i As Integer
Dim AdJust As Variant
Dim DisCou As Variant

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

Then we create a For Next loop that will run until the last entry and we assign this loop to a Select Case statement in order to calculate the incremented prices.

For i = 3 To LastRow
    Select Case Cells(i, 2).Value
        Case 6 To 15
            AdJust = 0.15
            Cells(i, 3).Value = Cells(i, 2).Value * AdJust

        Case 15.01 To 25
            AdJust = 0.25
            Cells(i, 3).Value = Cells(i, 2).Value * AdJust

        Case Is > 25.01
            AdJust = 0.35
            Cells(i, 3).Value = Cells(i, 2).Value * AdJust
    End Select

We can also calculate the discount prices in the same loop since we already assigned cells to calculate the incremented prices in the worksheet.

Select Case Cells(i, 4).Value
    Case 6 To 15
        DisCou = 0.1
        Cells(i, 5).Value = Cells(i, 4).Value * DisCou

    Case 15.01 To 22
        DisCou = 0.175
        Cells(i, 5).Value = Cells(i, 4).Value * DisCou

    Case 22.01 To 33
        DisCou = 0.25
        Cells(i, 5).Value = Cells(i, 4).Value * DisCou

    Case Is > 33.01
        DisCou = 0.3
        Cells(i, 5).Value = Cells(i, 4).Value * DisCou
End Select

Finally, we create a conditional to access if the discount price is actually a lowered price value. If this is true, we color the cell background green.
```
    If Cells(i, 7).Value < Cells(i, 2).Value Then

        Cells(i, 7).Interior.Color = RGB(140, 198, 63)

    End If
Next i
```

Consolidated Answer

    Sub Solution()

    Dim LastRow As Long
    Dim i As Integer
    Dim AdJust As Variant
    Dim DisCou As Variant

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row

    For i = 3 To LastRow

        Select Case Cells(i, 2).Value
            Case 6 To 15
                AdJust = 0.15
                Cells(i, 3).Value = Cells(i, 2).Value * AdJust
            Case 15.01 To 25
                AdJust = 0.25
                Cells(i, 3).Value = Cells(i, 2).Value * AdJust
            Case Is > 25.01
                AdJust = 0.35
                Cells(i, 3).Value = Cells(i, 2).Value * AdJust
        End Select

        Select Case Cells(i, 4).Value
            Case 6 To 15
                DisCou = 0.1
                Cells(i, 5).Value = Cells(i, 4).Value * DisCou
            Case 15.01 To 22
                DisCou = 0.175
                Cells(i, 5).Value = Cells(i, 4).Value * DisCou
            Case 22.01 To 33
                DisCou = 0.25
                Cells(i, 5).Value = Cells(i, 4).Value * DisCou
            Case Is > 33.01
                DisCou = 0.3
                Cells(i, 5).Value = Cells(i, 4).Value * DisCou  
        End Select

        If Cells(i, 7).Value < Cells(i, 2).Value Then
            Cells(i, 7).Interior.Color = RGB(140, 198, 63)      
        End If

    Next i

    End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED