Protocol Macro - final

Sub Main()

    'Sheets.Add(Before:=Sheets("Sheet0")).name = "Protocol"
    'CopyData
    'InsertColumns
    'FormattingProtocol

    'Dim lastRow As Integer
    'lastRow = Cells(Rows.Count, 1).End(xlUp).row
    'FillPracticalAndTheoreticalPoints (lastRow)

    'CoursePoints lastRow, 80, 20

    'Grade lastRow, 70, 30

End Sub

Function GetParticipants()

End Function

Sub SortData()
    Sheets("Protocol").Select
    Columns("C:C").Select
    ActiveWorkbook.Worksheets("Protocol").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Protocol").Sort.SortFields.Add2 Key:=Range( _
        "C1:C2712"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Protocol").Sort
        .SetRange Range("A2:E2712")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=0
End Sub

Sub Grade(lastRow, pointsFor5, pointsFor3 As Integer)
    Sheets("Protocol").Select
    '=IF(C=100,6,IF(C <=A,3+2*( C -B)/(A-B),5+( C -A)/(95-A)))
    'A - ìèíèìàëåí áðîé êóðñîâè òî÷êè çà îöåíêà 5
    'B - ìèíèìàëåí áðîé êóðñîâè òî÷êè çà îöåíêà 3
    'C - áðîé êóðñîâè òî÷êè (âçèìàò ñå îò ñúîòâåòíàòà êëåòêà ñëåä êàòî áúäàò èç÷èñëåíè)


    For r = 2 To lastRow
        If Range("B" & r) = "Did not participate in the exam" Then
            Range("C" & r) = ""
        ElseIf Range("B" & r) = 100 Then
            Range("C" & r) = 6
        ElseIf Range("B" & r) <= pointsFor5 Then
            Range("C" & r) = "=3 + 2 * (B" & r & " - " & pointsFor3 & ")/(" & pointsFor5 & " - " & pointsFor3 & ")"
            If Range("C" & r) < 2 Then
                Range("C" & r) = 2
            ElseIf Range("C" & r) > 6 Then
                Range("C" & r) = 6
            End If
        Else
            Range("C" & r) = "=5+(B" & r & " - " & pointsFor5 & ")/(95- " & pointsFor5 & ")"
            If Range("C" & r) > 6 Then
                Range("C" & r) = 6
            End If
        End If
    Next

End Sub

Sub CoursePoints(lastRow, percentPractical, percentTheoretical As Integer)
    Sheets("Protocol").Select
    '=$P*PP/ 100 + T*PT / 100
    '=D2*80/ 100 + E2*20 / 100
    'P - áðîé òî÷êè Ïðàêòè÷åñêè èçïèò
    'T - áðîé òî÷êè Òåîðåòè÷åí èçïèò
    'PP - ïðîöåíò îò Ïðàêòè÷åñêè èçïèò
    'PT - ïðîöåíò îò Òåîðåòè÷åí èçïèò

    Dim isNotParticipate As Boolean

    For r = 2 To lastRow
        isNotParticipate = Range("D" & r) = "NO" And Range("E" & r) = "NO"

        If isNotParticipate Then
            Range("B" & r) = "Did not participate in the exam"
        Else
            Range("B" & r) = _
        "=D" & r & "*" & percentPractical & " / 100 + E" & r & "*" & percentTheoretical & " / 100"
        End If
    Next
End Sub

Sub FillPracticalAndTheoreticalPoints(lastRow As Integer)
    '=VLOOKUP(A2,'Practical Points'!A:B,2,FALSE)

    Sheets("Protocol").Select
    For r = 2 To lastRow
        Range("D" & r) = "=VLOOKUP(A" & r & ",'Practical Points'!A:B,2,FALSE)"
        Range("E" & r) = "=VLOOKUP(A" & r & ",'Theoretical Points'!A:B,2,FALSE)"

        If Range("D" & r) = 0 And Range("E" & r) = 0 Then
            Range("D" & r) = "NO"
            Range("E" & r) = "NO"
        End If
    Next
End Sub

Sub InsertColumns()
    Sheets("Protocol").Select
    Range("B1") = "Course points"
    Range("C1") = "Grade"
    Range("D1") = "Practical Points"
    Range("E1") = "Theoretical Points"
End Sub

Sub CopyData()
    Dim col As Variant
    col = GetColumnsAddress("Sheet0", "Username")

    If col = False Then
        Msg.Box "Username not found"
    Else
        Sheets("Sheet0").Select
        Columns(col).Select
        Selection.Copy
        Sheets("Protocol").Select
        Columns(1).Select
        ActiveSheet.Paste
    End If
End Sub

Function GetColumnsAddress(sheetName, targetColName As String)
    Set col = Sheets(sheetName).Rows(1).Find(What:=targetColName)

    If col Is Nothing Then
        GetColumnsAddress = False
    Else
        GetColumnsAddress = col.Column
    End If
End Function

Sub FormattingProtocol()
    Sheets("Protocol").Select
    Range("A1:E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    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
    Selection.ColumnWidth = 30
    Range("A1:E1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
    End With
    Selection.Font.Size = 12
    Selection.Font.Size = 14
    Selection.Font.Bold = True
    Range("A1").Select
End Sub