Exam Preparation - Solutions

Exam Preparation - Solution
-----------------------------
Task 1 - Solution
-----------------------------
Sub Exam_Preparation_Task1_Solution()
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=100000,RC[-1]*3%,RC[-1]*2%)"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F99")
    Range("F2:F99").Select
    ActiveWindow.SmallScroll Down:=0
    Range("G2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G99")
    Range("G2:G99").Select
    ActiveWindow.SmallScroll Down:=0
    Rows("1:1").Select
    Selection.RowHeight = 55
    Range("A1:G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 15
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("A:G").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.SmallScroll Down:=77
    Range("C92").Select
    ActiveWindow.SmallScroll Down:=-77
    Range("A1:G99").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A1").Select
End Sub
---------------------------------
Task 2 - Solution
---------------------------------
Sub Exam_Preparation_Task2_Solution()

Dim ProductName As String
ProductName = Range("B1").Value

Dim ProductQuantity As Double
ProductQuantity = Range("B2").Value

Dim Town As String
Town = Range("B3").Value


' TotalPrice = ProductQuantity * SinglePrice

'SinglePrice -> Town, ProductName
Dim SinglePrice As Double
SinglePrice = 0


If Town = "Sofia" Then
    ' check which is the product bought in Sofia
    Select Case ProductName
        Case "coffee"
            SinglePrice = 2.5
        Case "water"
            SinglePrice = 1.4
        Case "juice"
            SinglePrice = 3.4
    End Select
ElseIf Town = "Varna" Then
     ' check which is the product bought in Varna
     Select Case ProductName
        Case "coffee"
            SinglePrice = 2.2
        Case "water"
            SinglePrice = 1.1
        Case "juice"
            SinglePrice = 3
    End Select
End If

' we know single price of the product
Dim TotalPrice As Double
TotalPrice = SinglePrice * ProductQuantity

Range("B4").Value = TotalPrice

End Sub
---------------------------------
Task 3 - Solution
---------------------------------
Sub Exam_Preparation_Task3_Solution()

Dim Row As Integer 'current row
Dim LastRow As Integer 'last row in table

Dim SumEven, SumOdd As Integer
SumEven = 0
SumOdd = 0

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

' we have all numbers
 For Row = 2 To LastRow
    Dim Number As Integer 'current number
    Number = Range("A" & Row).Value

    ' 1. write the type
    If Number Mod 2 = 0 Then
        ' number is even
        Range("B" & Row).Value = "even"
        SumEven = SumEven + Number
    Else
        ' number is odd
        Range("B" & Row).Value = "odd"
        SumOdd = SumOdd + Number
    End If

    '2. write last digit
    Dim LastDigit As Integer
    LastDigit = Number Mod 10
    Range("C" & Row).Value = LastDigit
Next Row

Range("F1").Value = SumEven
Range("F2").Value = SumOdd

End Sub
---------------------------------
Task 4 - Solution
---------------------------------
Sub Exam_Preparation_Task4_Solution()

    Dim InputText As String
    InputText = Range("A1").Value   ' "1 2 3 4 5 6"

    Dim Numbers As Variant
    Numbers = Split(InputText, " ") ' array of strings -> ["1", "2", "3", "4", "5", "6"]
    ' "1 2 3 4 5 6" -> Split " " -> ["1", "2", "3", "4", "5", "6"]

    Dim TargetNumber As Integer
    TargetNumber = Range("A2").Value

    Range("A3").Value = "Result:"

    Dim Result As String
    Result = "" 'number which are bigger than TargetNumber

    Dim Number As Variant ' every element in array
    For Each Number In Numbers
        'Number = "1"
        If CInt(Number) > TargetNumber Then
            Result = Result & Number & " "
        End If
    Next

    Range("B3").Value = Trim(Result)

End Sub