|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim ws As Worksheet
- Dim d As Object
- Set d1 = CreateObject("scripting.dictionary")
- Set d = CreateObject("scripting.dictionary")
- For Each ws In Worksheets(Array("表1", "表2"))
- With ws
- r = .Cells(.Rows.Count, 3).End(xlUp).Row
- arr = .Range("a4:g" & r)
- For i = 1 To UBound(arr)
- If arr(i, 6) = "优秀" And arr(i, 7) = "" Then
- xm = CStr(arr(i, 5))
- If Not d1.exists(xm) Then
- Set d1(xm) = CreateObject("scripting.dictionary")
- End If
- d1(xm)(ws.Name) = ""
- End If
- Next
- End With
- Next
- For Each aa In d1.keys
- If d1(aa).Count = 1 Then
- d1.Remove (aa)
- End If
- Next
- With Worksheets("表3")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a4:f" & r)
- m = 0
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 2)) Then
- m = m + 1
- ReDim brr(1 To 7)
- brr(1) = m
- brr(2) = arr(i, 2)
- Else
- brr = d(arr(i, 2))
- End If
- xm = arr(i, 5)
- If d1.exists(xm) Then
- brr(5) = brr(5) & "," & arr(i, 3)
- brr(6) = brr(6) + 1
- Else
- brr(3) = brr(3) & "," & arr(i, 3)
- brr(4) = brr(4) + 1
- End If
- d(arr(i, 2)) = brr
- Next
- End With
- With Worksheets("生成报表")
- .UsedRange.Offset(5, 0).Clear
- m = 5
- For Each aa In d.keys
- brr = d(aa)
- If Len(brr(3)) <> 0 Then
- brr(3) = Mid(brr(3), 2)
- End If
- If Len(brr(5)) <> 0 Then
- brr(5) = Mid(brr(5), 2)
- End If
- m = m + 1
- .Cells(m, 1).Resize(1, 7) = brr
- Next
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- .Range("a4:g" & r).Borders.LineStyle = xlContinuous
- .Range("c6:c" & r & ",e6:e" & r).WrapText = True
- .Rows("6:" & r).AutoFit
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|