Advertisement
veronikaaa86

Protocol Macro - final

Oct 4th, 2022 (edited)
978
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub Main()
  2.  
  3.     'Sheets.Add(Before:=Sheets("Sheet0")).name = "Protocol"
  4.    'CopyData
  5.    'InsertColumns
  6.    'FormattingProtocol
  7.    
  8.     'Dim lastRow As Integer
  9.    'lastRow = Cells(Rows.Count, 1).End(xlUp).row
  10.    'FillPracticalAndTheoreticalPoints (lastRow)
  11.    
  12.     'CoursePoints lastRow, 80, 20
  13.    
  14.     'Grade lastRow, 70, 30
  15.    
  16. End Sub
  17.  
  18. Function GetParticipants()
  19.    
  20. End Function
  21.  
  22. Sub SortData()
  23.     Sheets("Protocol").Select
  24.     Columns("C:C").Select
  25.     ActiveWorkbook.Worksheets("Protocol").Sort.SortFields.Clear
  26.     ActiveWorkbook.Worksheets("Protocol").Sort.SortFields.Add2 Key:=Range( _
  27.         "C1:C2712"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
  28.         xlSortNormal
  29.     With ActiveWorkbook.Worksheets("Protocol").Sort
  30.         .SetRange Range("A2:E2712")
  31.         .Header = xlNo
  32.         .MatchCase = False
  33.         .Orientation = xlTopToBottom
  34.         .SortMethod = xlPinYin
  35.         .Apply
  36.     End With
  37.     ActiveWindow.SmallScroll Down:=0
  38. End Sub
  39.  
  40. Sub Grade(lastRow, pointsFor5, pointsFor3 As Integer)
  41.     Sheets("Protocol").Select
  42.     '=IF(C=100,6,IF(C <=A,3+2*( C -B)/(A-B),5+( C -A)/(95-A)))
  43.    'A - ìèíèìàëåí áðîé êóðñîâè òî÷êè çà îöåíêà 5
  44.    'B - ìèíèìàëåí áðîé êóðñîâè òî÷êè çà îöåíêà 3
  45.    'C - áðîé êóðñîâè òî÷êè (âçèìàò ñå îò ñúîòâåòíàòà êëåòêà ñëåä êàòî áúäàò èç÷èñëåíè)
  46.  
  47.    
  48.     For r = 2 To lastRow
  49.         If Range("B" & r) = "Did not participate in the exam" Then
  50.             Range("C" & r) = ""
  51.         ElseIf Range("B" & r) = 100 Then
  52.             Range("C" & r) = 6
  53.         ElseIf Range("B" & r) <= pointsFor5 Then
  54.             Range("C" & r) = "=3 + 2 * (B" & r & " - " & pointsFor3 & ")/(" & pointsFor5 & " - " & pointsFor3 & ")"
  55.             If Range("C" & r) < 2 Then
  56.                 Range("C" & r) = 2
  57.             ElseIf Range("C" & r) > 6 Then
  58.                 Range("C" & r) = 6
  59.             End If
  60.         Else
  61.             Range("C" & r) = "=5+(B" & r & " - " & pointsFor5 & ")/(95- " & pointsFor5 & ")"
  62.             If Range("C" & r) > 6 Then
  63.                 Range("C" & r) = 6
  64.             End If
  65.         End If
  66.     Next
  67.    
  68. End Sub
  69.  
  70. Sub CoursePoints(lastRow, percentPractical, percentTheoretical As Integer)
  71.     Sheets("Protocol").Select
  72.     '=$P*PP/ 100 + T*PT / 100
  73.    '=D2*80/ 100 + E2*20 / 100
  74.    'P - áðîé òî÷êè Ïðàêòè÷åñêè èçïèò
  75.    'T - áðîé òî÷êè Òåîðåòè÷åí èçïèò
  76.    'PP - ïðîöåíò îò Ïðàêòè÷åñêè èçïèò
  77.    'PT - ïðîöåíò îò Òåîðåòè÷åí èçïèò
  78.    
  79.     Dim isNotParticipate As Boolean
  80.    
  81.     For r = 2 To lastRow
  82.         isNotParticipate = Range("D" & r) = "NO" And Range("E" & r) = "NO"
  83.        
  84.         If isNotParticipate Then
  85.             Range("B" & r) = "Did not participate in the exam"
  86.         Else
  87.             Range("B" & r) = _
  88.         "=D" & r & "*" & percentPractical & " / 100 + E" & r & "*" & percentTheoretical & " / 100"
  89.         End If
  90.     Next
  91. End Sub
  92.  
  93. Sub FillPracticalAndTheoreticalPoints(lastRow As Integer)
  94.     '=VLOOKUP(A2,'Practical Points'!A:B,2,FALSE)
  95.    
  96.     Sheets("Protocol").Select
  97.     For r = 2 To lastRow
  98.         Range("D" & r) = "=VLOOKUP(A" & r & ",'Practical Points'!A:B,2,FALSE)"
  99.         Range("E" & r) = "=VLOOKUP(A" & r & ",'Theoretical Points'!A:B,2,FALSE)"
  100.        
  101.         If Range("D" & r) = 0 And Range("E" & r) = 0 Then
  102.             Range("D" & r) = "NO"
  103.             Range("E" & r) = "NO"
  104.         End If
  105.     Next
  106. End Sub
  107.  
  108. Sub InsertColumns()
  109.     Sheets("Protocol").Select
  110.     Range("B1") = "Course points"
  111.     Range("C1") = "Grade"
  112.     Range("D1") = "Practical Points"
  113.     Range("E1") = "Theoretical Points"
  114. End Sub
  115.  
  116. Sub CopyData()
  117.     Dim col As Variant
  118.     col = GetColumnsAddress("Sheet0", "Username")
  119.    
  120.     If col = False Then
  121.         Msg.Box "Username not found"
  122.     Else
  123.         Sheets("Sheet0").Select
  124.         Columns(col).Select
  125.         Selection.Copy
  126.         Sheets("Protocol").Select
  127.         Columns(1).Select
  128.         ActiveSheet.Paste
  129.     End If
  130. End Sub
  131.  
  132. Function GetColumnsAddress(sheetName, targetColName As String)
  133.     Set col = Sheets(sheetName).Rows(1).Find(What:=targetColName)
  134.    
  135.     If col Is Nothing Then
  136.         GetColumnsAddress = False
  137.     Else
  138.         GetColumnsAddress = col.Column
  139.     End If
  140. End Function
  141.  
  142. Sub FormattingProtocol()
  143.     Sheets("Protocol").Select
  144.     Range("A1:E1").Select
  145.     Range(Selection, Selection.End(xlDown)).Select
  146.     Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  147.     Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  148.     With Selection.Borders(xlEdgeLeft)
  149.         .LineStyle = xlContinuous
  150.         .Weight = xlThin
  151.     End With
  152.     With Selection.Borders(xlEdgeTop)
  153.         .LineStyle = xlContinuous
  154.         .Weight = xlThin
  155.     End With
  156.     With Selection.Borders(xlEdgeBottom)
  157.         .LineStyle = xlContinuous
  158.         .Weight = xlThin
  159.     End With
  160.     With Selection.Borders(xlEdgeRight)
  161.         .LineStyle = xlContinuous
  162.         .Weight = xlThin
  163.     End With
  164.     With Selection.Borders(xlInsideVertical)
  165.         .LineStyle = xlContinuous
  166.         .Weight = xlThin
  167.     End With
  168.     With Selection.Borders(xlInsideHorizontal)
  169.         .LineStyle = xlContinuous
  170.         .Weight = xlThin
  171.     End With
  172.     Selection.ColumnWidth = 30
  173.     Range("A1:E1").Select
  174.     With Selection.Interior
  175.         .Pattern = xlSolid
  176.         .PatternColorIndex = xlAutomatic
  177.         .ThemeColor = xlThemeColorDark2
  178.     End With
  179.     With Selection
  180.         .HorizontalAlignment = xlGeneral
  181.         .VerticalAlignment = xlCenter
  182.         .ReadingOrder = xlContext
  183.     End With
  184.     With Selection
  185.         .HorizontalAlignment = xlCenter
  186.         .VerticalAlignment = xlCenter
  187.         .ReadingOrder = xlContext
  188.     End With
  189.     Selection.Font.Size = 12
  190.     Selection.Font.Size = 14
  191.     Selection.Font.Bold = True
  192.     Range("A1").Select
  193. End Sub
  194.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement