exam_final

Sub Main()
    Dim lastRow As Integer
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    InsertNewColumn
    SplitName (lastRow)
    DeleteColumn
    ExperiencePlus (lastRow)
    GrossSalary (lastRow)
    Calculations (lastRow)
    AvgAndSum
    PercentAvgSalary (lastRow)
    Formatting (lastRow)

End Sub

Sub InsertNewColumn()
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Име"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Фамилия"
    Range("A1").Select
End Sub

Sub SplitName(lastRow As Integer)
    Dim fullName, firstName, lastName As String

    For i = 2 To lastRow
        fullName = Range("A" & i)

        Dim splitedName As Variant
        splitedName = Split(fullName, " ")

        Range("B" & i) = splitedName(0)
        Range("C" & i) = splitedName(1)
    Next
End Sub

Sub DeleteColumn()
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub

Sub ExperiencePlus(lastRow As Integer)
    For i = 2 To lastRow
        Dim baseSalary, experienceAge As Double
        baseSalary = Range("E" & i)
        experienceAge = Range("D" & i)

        If experienceAge >= 3 Then
            'Range("F" & i) = experienceAge * (baseSalary * 0.006)
            Range("F" & i) = "=D" & i & "*(E" & i & "*0.006)"
        Else
            Range("F" & i) = 0
        End If
    Next
End Sub

Sub GrossSalary(lastRow As Integer)
    For i = 2 To lastRow
        Range("G" & i) = "=E" & i & "+F" & i
    Next
End Sub

Sub Calculations(lastRow As Integer)
    For i = 2 To lastRow
        Range("H" & i) = "=G" & i & "*0.224"
        Range("I" & i) = "=G" & i & "- H" & i
        Range("K" & i) = "=G" & i & "*0.189"
        Range("L" & i) = "=G" & i & "+ K" & i
    Next
End Sub

Sub AvgAndSum()
    Range("A60:H60").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Средно"
    Range("A60:H60").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("I60").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-58]C:R[-1]C)"
    Range("I61").Select
    ActiveWindow.SmallScroll Down:=3
    Range("J60:K60").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    ActiveCell.FormulaR1C1 = "Общо"
    Range("L60").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-58]C:R[-1]C)"
    Range("L61").Select
End Sub

Sub PercentAvgSalary(lastRow As Integer)
    For i = 2 To lastRow
        Range("J" & i) = "=(I" & i & "- $I$" & lastRow + 1 & ") / $I$" & lastRow + 1
    Next
End Sub

Sub Formatting(lastRow As Integer)
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1:L1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Служители на трудов договор - извадка"
    Range("A1:L1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Size = 12
    Selection.Font.Size = 14
    Selection.Font.Size = 16
    Selection.Font.Size = 18
    Selection.Font.Size = 20
    Range("A2:L2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    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
    ActiveWindow.SmallScroll Down:=39
    Range("A61:H61,J61:K61").Select
    Range("J61").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Range("I61,L61").Select
    Range("L61").Activate
    Selection.Font.Bold = True
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.499984740745262
        .PatternTintAndShade = 0
    End With
    Range("A1:L61").Select
    Range("A61").Activate
    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
    ActiveWindow.SmallScroll Down:=-69
    Range("A1:L1").Select
End Sub