Sub Отчет по успеваемости()
S = Range(Cells(1, 11), Cells(1, 11)).Text
Worksheets("Предмет1".Visible = True
Sheets("Предмет1".Select
DDD = ""
A = 0
B = Range(Cells(3, 2), Cells(53, 2)).Count
For i = 3 To B + 2
If Range(Cells(i, 2), Cells(i, 2)).Text = S Then
For j = 5 To 60
If Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "i" Then
A = A + 1
ElseIf Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "" Then
Else
DDD = DDD + Range(Cells(i, j), Cells(i, j)).Offset(-(i - 1), 0).FormulaR1C1 + ", "
End If
Next j
Sheets("Отчет по успеваемости".Select
Range(Cells(4, 2), Cells(4, 2)).FormulaR1C1 = DDD
End If
Next i
Worksheets("Предмет2".Visible = True
Worksheets("Предмет1".Visible = False
Sheets("Предмет2".Select
DDD = ""
For i = 3 To B + 2
If Range(Cells(i, 2), Cells(i, 2)).Text = S Then
For j = 5 To 60
If Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "i" Then
A = A + 1
ElseIf Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "" Then
Else
DDD = DDD + Range(Cells(i, j), Cells(i, j)).Offset(-(i - 1), 0).FormulaR1C1 + ", "
End If
Next j
Sheets("Отчет по успеваемости".Select
Range(Cells(5, 2), Cells(5, 2)).FormulaR1C1 = DDD
End If
Next i
Worksheets("Предмет3".Visible = True
Worksheets("Предмет2".Visible = False
Sheets("Предмет3".Select
DDD = ""
For i = 3 To B + 2
If Range(Cells(i, 2), Cells(i, 2)).Text = S Then
For j = 5 To 60
If Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "i" Then
A = A + 1
ElseIf Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "" Then
Else
DDD = DDD + Range(Cells(i, j), Cells(i, j)).Offset(-(i - 1), 0).FormulaR1C1 + ", "
End If
Next j
Sheets("Отчет по успеваемости".Select
Range(Cells(6, 2), Cells(6, 2)).FormulaR1C1 = DDD
End If
Next i
Worksheets("Предмет4".Visible = True
Worksheets("Предмет3".Visible = False
Sheets("Предмет4".Select
DDD = ""
For i = 3 To B + 2
If Range(Cells(i, 2), Cells(i, 2)).Text = S Then
For j = 5 To 60
If Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "i" Then
A = A + 1
ElseIf Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "" Then
Else
DDD = DDD + Range(Cells(i, j), Cells(i, j)).Offset(-(i - 1), 0).FormulaR1C1 + ", "
End If
Next j
Sheets("Отчет по успеваемости".Select
Range(Cells(7, 2), Cells(7, 2)).FormulaR1C1 = DDD
End If
Next i
Worksheets("Предмет5".Visible = True
Worksheets("Предмет4".Visible = False
Sheets("Предмет5".Select
DDD = ""
For i = 3 To B + 2
If Range(Cells(i, 2), Cells(i, 2)).Text = S Then
For j = 5 To 60
If Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "i" Then
A = A + 1
ElseIf Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "" Then
Else
DDD = DDD + Range(Cells(i, j), Cells(i, j)).Offset(-(i - 1), 0).FormulaR1C1 + ", "
End If
Next j
Sheets("Отчет по успеваемости".Select
Range(Cells(8, 2), Cells(8, 2)).FormulaR1C1 = DDD
End If
Next i
Worksheets("Предмет6".Visible = True
Worksheets("Предмет5".Visible = False
Sheets("Предмет6".Select
DDD = ""
For i = 3 To B + 2
If Range(Cells(i, 2), Cells(i, 2)).Text = S Then
For j = 5 To 60
If Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "i" Then
A = A + 1
ElseIf Range(Cells(i, j), Cells(i, j)).FormulaR1C1 = "" Then
Else
DDD = DDD + Range(Cells(i, j), Cells(i, j)).Offset(-(i - 1), 0).FormulaR1C1 + ", "
End If
Next j
Sheets("Отчет по успеваемости".Select
Range(Cells(10, 2), Cells(10, 2)).FormulaR1C1 = A
Range(Cells(9, 2), Cells(9, 2)).FormulaR1C1 = DDD
End If
Next i
Worksheets("Предмет6".Visible = False
End Sub |