写了1个,目标表2原理基本相同,楼主可以参照编写.
- Sub 考核表1()
- Dim d As Object, Sht As Worksheet, arA, arB(1 To 1000, 1 To 11), x%, y%
- Set d = CreateObject("Scripting.Dictionary")
- [a1].CurrentRegion.Offset(1).ClearContents
- For x = 9 To 11
- d(CStr(Cells(1, x))) = x
- Next
- For Each Sht In Worksheets
- If Sht.Name <> "目标表1" And Sht.Name <> "目标表2" Then
- arA = Sht.UsedRange
- For x = 2 To UBound(arA)
- If Not d.Exists(arA(x, 1)) Then
- y = y + 1
- d(arA(x, 1)) = y
- arB(y, 1) = arA(x, 1)
- End If
- arB(d(arA(x, 1)), 2) = arB(d(arA(x, 1)), 2) + 1
- arB(d(arA(x, 1)), 3) = IIf(arB(d(arA(x, 1)), 3) = "", _
- Sht.Name, arB(d(arA(x, 1)), 3) & "," & Sht.Name)
- If d.Exists(Sht.Name) Then arB(d(arA(x, 1)), d(Sht.Name)) = arA(x, 2)
- Select Case arA(x, 2)
- Case "优秀"
- arB(d(arA(x, 1)), 4) = arB(d(arA(x, 1)), 4) + 1
- arB(d(arA(x, 1)), 5) = IIf(arB(d(arA(x, 1)), 5) = "", _
- Sht.Name, arB(d(arA(x, 1)), 5) & "," & Sht.Name)
- Case "合格"
- arB(d(arA(x, 1)), 6) = arB(d(arA(x, 1)), 6) + 1
- arB(d(arA(x, 1)), 7) = IIf(arB(d(arA(x, 1)), 7) = "", _
- Sht.Name, arB(d(arA(x, 1)), 7) & "," & Sht.Name)
- End Select
- Next
- End If
- Next
- [a2].Resize(y, 11) = arB
- End Sub
复制代码 |