|
- Option Explicit
- Sub 主程序()
- Dim sht As Worksheet
- For Each sht In Sheets
- If sht.Name = "成绩总表(" & Sheets("基本信息设置").Range("d2").Value & ")" Then
- If MsgBox("该表已存在,是否覆盖?", 33, "提醒!") = vbOK Then
- Application.DisplayAlerts = False
- Sheets("成绩总表(" & Sheets("基本信息设置").Range("d2").Value & ")").Delete
- Application.DisplayAlerts = True
- Exit For
- Else
- Exit Sub
- End If
- End If
- Next
- Call 生成年级成绩
- End Sub
- Sub 生成年级成绩()
- Dim i, r, arow&
-
- Call 清除格式
-
- Sheets("mb").Copy after:=Sheets("基本信息设置")
- ActiveSheet.Name = "成绩总表(" & Sheets("基本信息设置").Range("d2").Value & ")"
- Application.ScreenUpdating = False
- r = 5
- For i = 4 To Sheets("原始成绩").Range("a" & Rows.Count).End(xlUp).Row
- If Sheets("原始成绩").Range("C" & i).Value = Left(Sheets("基本信息设置").Range("d2").Value, 1) Then
- Range("A" & r).Value = Sheets("原始成绩").Range("B" & i).Value
- Range("B" & r).Value = Sheets("原始成绩").Range("C" & i).Value
- Range("C" & r).Value = Sheets("原始成绩").Range("D" & i).Value
- Range("D" & r).Value = Sheets("原始成绩").Range("E" & i).Value
- Range("E" & r).Value = Sheets("原始成绩").Range("F" & i).Value
- Range("F" & r).Value = Sheets("原始成绩").Range("G" & i).Value
- Range("J" & r).Value = Sheets("原始成绩").Range("H" & i).Value
- Range("N" & r).Value = Sheets("原始成绩").Range("I" & i).Value
- Range("A" & r).Value = Sheets("原始成绩").Range("B" & i).Value
- Range("R" & r).Value = Sheets("原始成绩").Range("J" & i).Value
- Range("V" & r).Value = Sheets("原始成绩").Range("K" & i).Value
- Range("Z" & r).Value = Sheets("原始成绩").Range("L" & i).Value
- Range("AD" & r).Value = Sheets("原始成绩").Range("M" & i).Value
- Range("AH" & r).Value = Sheets("原始成绩").Range("N" & i).Value
- Range("AL" & r).Value = Sheets("原始成绩").Range("O" & i).Value
- r = r + 1
- End If
- Next
-
- arow = Range("a" & Rows.Count).End(xlUp).Row
-
- Range("A5:AO" & arow).Select
-
- 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
-
- With Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
-
- For i = 5 To arow
- Range("G" & i).Value = Application.WorksheetFunction.Rank(Range("F" & i).Value, Range("F" & uprow(Range("C" & i)) & ":F" & downrow(Range("C" & i))), 0) '班级排名
- Range("K" & i).Value = Application.WorksheetFunction.Rank(Range("J" & i).Value, Range("J" & uprow(Range("C" & i)) & ":J" & downrow(Range("C" & i))), 0)
- Range("O" & i).Value = Application.WorksheetFunction.Rank(Range("N" & i).Value, Range("N" & uprow(Range("C" & i)) & ":N" & downrow(Range("C" & i))), 0)
- Range("S" & i).Value = Application.WorksheetFunction.Rank(Range("R" & i).Value, Range("R" & uprow(Range("C" & i)) & ":R" & downrow(Range("C" & i))), 0)
- Range("AI" & i).Value = Application.WorksheetFunction.Rank(Range("AH" & i).Value, Range("AH" & uprow(Range("C" & i)) & ":AH" & downrow(Range("C" & i))), 0)
- Range("AM" & i).Value = Application.WorksheetFunction.Rank(Range("AL" & i).Value, Range("AL" & uprow(Range("C" & i)) & ":AL" & downrow(Range("C" & i))), 0)
-
- Range("H" & i).Value = Application.WorksheetFunction.Rank(Range("F" & i).Value, Range("F" & uprow(Range("A" & i)) & ":F" & downrow(Range("A" & i))), 0) '校级排名
- Range("L" & i).Value = Application.WorksheetFunction.Rank(Range("J" & i).Value, Range("J" & uprow(Range("A" & i)) & ":J" & downrow(Range("A" & i))), 0)
- Range("P" & i).Value = Application.WorksheetFunction.Rank(Range("N" & i).Value, Range("N" & uprow(Range("A" & i)) & ":N" & downrow(Range("A" & i))), 0)
- Range("T" & i).Value = Application.WorksheetFunction.Rank(Range("R" & i).Value, Range("R" & uprow(Range("A" & i)) & ":R" & downrow(Range("A" & i))), 0)
- Range("AJ" & i).Value = Application.WorksheetFunction.Rank(Range("AH" & i).Value, Range("AH" & uprow(Range("A" & i)) & ":AH" & downrow(Range("A" & i))), 0)
- Range("AN" & i).Value = Application.WorksheetFunction.Rank(Range("AL" & i).Value, Range("AL" & uprow(Range("A" & i)) & ":AL" & downrow(Range("A" & i))), 0)
-
- Range("I" & i).Value = Application.WorksheetFunction.Rank(Range("F" & i).Value, Range("F5:F" & arow), 0) '总排名
- Range("M" & i).Value = Application.WorksheetFunction.Rank(Range("J" & i).Value, Range("J5:J" & arow), 0)
- Range("Q" & i).Value = Application.WorksheetFunction.Rank(Range("N" & i).Value, Range("N5:N" & arow), 0)
- Range("U" & i).Value = Application.WorksheetFunction.Rank(Range("R" & i).Value, Range("R5:R" & arow), 0)
- Range("AK" & i).Value = Application.WorksheetFunction.Rank(Range("AH" & i).Value, Range("AH5:AH" & arow), 0)
- Range("AO" & i).Value = Application.WorksheetFunction.Rank(Range("AL" & i).Value, Range("AL5:AL" & arow), 0)
- Next
-
-
- If Sheets("基本信息设置").Range("d2").Value <> "一年级" And Sheets("基本信息设置").Range("d2").Value <> "二年级" Then
-
- For i = 5 To arow
-
- Range("W" & i).Value = Application.WorksheetFunction.Rank(Range("V" & i).Value, Range("V" & uprow(Range("C" & i)) & ":V" & downrow(Range("C" & i))), 0)
- Range("AA" & i).Value = Application.WorksheetFunction.Rank(Range("Z" & i).Value, Range("Z" & uprow(Range("C" & i)) & ":Z" & downrow(Range("C" & i))), 0)
- Range("AE" & i).Value = Application.WorksheetFunction.Rank(Range("AD" & i).Value, Range("AD" & uprow(Range("C" & i)) & ":AD" & downrow(Range("C" & i))), 0)
-
- Range("X" & i).Value = Application.WorksheetFunction.Rank(Range("V" & i).Value, Range("V" & uprow(Range("A" & i)) & ":V" & downrow(Range("A" & i))), 0)
- Range("AB" & i).Value = Application.WorksheetFunction.Rank(Range("Z" & i).Value, Range("Z" & uprow(Range("A" & i)) & ":Z" & downrow(Range("A" & i))), 0)
- Range("AF" & i).Value = Application.WorksheetFunction.Rank(Range("AD" & i).Value, Range("AD" & uprow(Range("A" & i)) & ":AD" & downrow(Range("A" & i))), 0)
-
- Range("Y" & i).Value = Application.WorksheetFunction.Rank(Range("V" & i).Value, Range("V5:V" & arow), 0)
- Range("AC" & i).Value = Application.WorksheetFunction.Rank(Range("Z" & i).Value, Range("Z5:Z" & arow), 0)
- Range("AG" & i).Value = Application.WorksheetFunction.Rank(Range("AD" & i).Value, Range("AD5:AD" & arow), 0)
-
- Next
- Else
- Columns("V:AG").Delete
-
- End If
- Application.ScreenUpdating = True
- Range("A5").Select
- End Sub
- Function uprow(ByVal aaa As Range)
- Dim h, l, i&
- h = aaa.Row
- l = aaa.Column
- For i = aaa.Row To 1 Step -1
- If aaa.Value <> Cells(i, l).Value Then Exit For
- Next
- uprow = i + 1
- End Function
- Function downrow(ByVal aaa As Range)
- Dim h, l, i&
- h = aaa.Row
- l = aaa.Column
- For i = aaa.Row To Rows.Count
- If aaa.Value <> Cells(i, l).Value Then Exit For
- Next
- downrow = i - 1
- End Function
- Sub 清除格式()
- Dim s As Style
- Application.ScreenUpdating = False
- On Error Resume Next
- For Each s In ThisWorkbook.Styles
- If Not s.BuiltIn Then s.Delete
- Next
- Application.ScreenUpdating = True
- End Sub
复制代码 我也是新手,刚刚学习VBA,可能写得比较啰嗦,你看看可不可以用
|
|