Sub Classes Individual Sections progress () Dim drank As Object Set drank = CreateObject ("Scripting.Dictionary") Dim dstd As Object Set D STD = CreateObject ("Scripting.Dictionary") Dim dsbj as Object Set dsbj = CreateObject ("Scripting.Dictionary") EM = Array ("Monthly Exam 2", "midterm") for n = LBound (em) to UBound (EM) Step 1 Set sht = thisworkbook.worksheets ("Score Table _" & EM (n)) with sht Endrow =. Cells (. Cells.Rows.Count, 1). End (Xlup). Row Endcol =. Cells (1,. Cells.Columns.Count). End (xlToLeft). Column for i = 2 to Endrow Key = CStr (. Cells (i, 1). Value) dstd (Key) = Array (CStr (. Cells (i, 1). Value), CStr (. Cells (i, 2). Text), CStr (. Cells (i, 3). Text)) for J = 1 to Endcol If. Cells (1, J). Text like "* Platoon" then DSBJ (. Cells (1, J). Text) = "" "End If Key = CStr (. Cells (i, 1). Value) & ";" & Em (n) &. Cells (1,J). Text ' Debug.Print key Drank (key) =. Cells (i, J). Value Next J Next i End with next n for each K in DSBJ. Keys Set sht = Createsheet (ThisWorkbook, K & "_ Leap Progress _ I ^_^") with Sht. Range ("A1"). Resize (1, 6). Value = Array ("Test number", "Name", "Class", EM (0), EM (1), "retreat") Endcol =. Cells (1,. Cells.Columns.Count). End (xlToLeft). Column i = 1 for each std in Dstd.keys i = i + 1 Ar = dstd (STD). Cells (i, 1). Value = Ar (0). Cells (i, 2). Value = Ar (1). Cells (i, 3). Value = AR (2) Key = CStr (AR (0)) & ";" &. Cells (1, 4). Text & Split (. Name, "_") (0). Cells (i, 4). Value = Drank (key) key = CStr (Ar (0)) & ";" &. Cells (1, 5). Text & Split (. Name, "_") (0). Cells (i, 5). Value = Drank (Key). Cells (i, 6) = Val (. Cells (i, 4). Value)-Val (. Cells (i, 5). Value) Next std sort_rank. UsedRange, True. Columns.AutoFit End With Next K set dsbj = no set dstd = Nothing Set drank = Nothing End Su Bpublic Sub Sort_classrank (ByVal rng as Range, Optional withheader as Boolean = True) with Rng. Sort _ Key1:=rng.cells (1, 3), order1:=xlascending, _ Key2:=rng.cells (1, 6), order2:=xldescending, _ Header:=iif (Withheader, XlYes, XlNo), Matchcase:=false, Orientation:=xltoptobottom, Sortmethod:=xlpinyin End WithEnd SubPublic Sub Sort_rank (ByVal rng as Range, Optional withheader as Boolean = True) with Rng. Sort _ Key1:=rng.cells (1, 6), order1:=xldescending, _ Header:=iif (Withheader, XlYes, XlNo), Matchcas E:=false, Orientation:=xltoptobottom, Sortmethod:=xlpinyin End withend Sub
20171104xlVBA each of the various branches of the retreat