Sub MyStuNames()
On Error Resume Next
Application.ScreenUpdating = False
Set rng1 = Worksheets("StudNames"): Set rng2 = Worksheets("Analysis")
S = Mid(rng2.[AB1], 1, Len(rng2.[AB1]) - 1) & "-" & Right(rng2.[AB1], 1): t = rng2.[AB1]
S2 = Mid(rng2.[AB1], 1, Len(rng2.[AB1]) - 1) & "/" & Right(rng2.[AB1], 1): t2 = rng2.[AB1]
X = Application.CountIf(rng1.Range("B:B"), S) + Application.CountIf(rng1.Range("B:B"), t) + Application.CountIf(rng1.Range("B:B"), S2)
Y = IIf(Range("LangCod") = 2, 5, 4)
rng2.Range("B8:C42") = Empty
For i = 1 To X
rng2.Cells(7 + i, "B").Value = i
For Each cel In rng1.Range("B2:B5000")
If (cel = S Or cel = t Or cel = t1) And cel.Offset(0, -1) = i Then _
rng2.Cells(7 + i, "C").Value = rng1.Cells(cel.Row, Y).Value
Next
Next
Application.ScreenUpdating = True
End Sub
هكذا؟