Excel Automation - Regular Exam - Solutions

Excel Automation - Regular Exam - Solutions
-------------------------------------------
Task 01
------------------------------------------
Sub Regular_Exam_Task1_Solution()

' Regular_Exam_Task1_Solution Macro
'
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Days"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=DAYS(RC[-1],RC[-2])"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K264")
    Range("K2:K264").Select
    Columns("K:K").Select
    Selection.NumberFormat = "General"
    Range("A1:O264").Select
    Range("J4").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
    End With
    Range("A1:O1").Select
       With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
    End With
    Rows("1:1").RowHeight = 40.5
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .ThemeColor = xlThemeColorLight1
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .ReadingOrder = xlContext
    End With

    Range("A1:O264").Select
    Range("A1").Activate
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
     With Selection
        .VerticalAlignment = xlCenter
        .WrapText = True
        .ReadingOrder = xlContext
    End With

End Sub
------------------------------------
Task 02
------------------------------------
Sub Regular_Exam_Task2_Solution()

Dim CompanyName As String, Salary As Integer, Bonus As Integer
CompanyName = Range("B1").Value

If CompanyName = "SoftUni" Then
    Salary = 2500
    Bonus = 1200
ElseIf CompanyName = "Microsoft" Then
    Salary = 2700
    Bonus = 1400
ElseIf CompanyName = "SAP" Or CompanyName = "sap" Then
    Salary = 3100
    Bonus = 1500
End If


Range("B2").Value = Salary
Range("B3").Value = Bonus

End Sub
-------------------------------
Task 03
-------------------------------
Sub Regular_Exam_Task3_Solution()

Dim StartNumber As Integer, EndNumber As Integer, NumbersRange As String, Sum As Integer

StartNumber = Range("B1").Value
EndNumber = Range("B2").Value

Dim Num As Integer
For Num = StartNumber To EndNumber
    NumbersRange = NumbersRange & Num & " "
    Sum = Sum + Num
Next Num

Range("B3").Value = Trim(NumbersRange)
Range("B4").Value = Sum

End Sub
----------------------------------
Task 04
----------------------------------
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