错题统计代码

Sub 错题统计()

Dim i, j, d

Set d = CreateObject("Scripting.Dictionary")

For i = 2 To [a65536].End(3).Row

  For j = 5 To 12

      If Cells(i, j) <> "" Then

          If InStr(Cells(i, j), "|") Then

              For k = 0 To UBound(Split(Cells(i, j), "|"))

              d(Val(Split(Cells(i, j), "|")(k))) = d(Val(Split(Cells(i, j), "|")(k))) + 1

              Next k

          Else

              d(Cells(i, j).Value) = d(Cells(i, j).Value) + 1

          End If

          End If

          Next j, i

Dim sh As Worksheet

On Error Resume Next

If Sheets("统计结果") Is Nothing Then

Set sh = Sheets.Add

With sh

.Name = "统计结果"

Dim qNum As Integer

qNum = 75

.Range("A1") = "题号"

.Range("B1") = "类型"

.Range("C1") = "出现次数"

'.Range("A1:B1").Locked = True

For i = 1 To qNum

    .Range("a" & 1 + i) = i

    '.Range("a" & 1 + i).Locked = False

    If d.Exists(i) Then

        .Range("c" & 1 + i) = d(i)

    Else

        .Range("c" & 1 + i) = "全对"

    End If

    '.Range("b" & 1 + i).Locked = False

Next i

.Range("B2:B16") = "听力"

.Range("B7:B16").Interior.ColorIndex = 44

.Range("B17:B31") = "完型"

.Range("B32:B46") = "阅读"

.Range("B32:B46").Interior.ColorIndex = 44

.Range("B47:B51") = "选词"

.Range("B52:B61") = "单词"

.Range("B62:B71") = "语法填空"

.Range("B72:B76") = "任务阅读"

.Range("A:A").Font.ColorIndex = 3

.Range("A1:C76").HorizontalAlignment = Excel.xlCenter

.Range("A1:C76").Borders.LineStyle = Excel.xlContinuous

.Range("A1:C76").Borders.ColorIndex = 1

.Range("A1:C76").Borders.Weight = Excel.xlThin

'.Protect

End With

End If

End Sub