|
- Sub Macro1()
- On Error Resume Next
- Dim arr, brr, d, ar, dr, cr, i&, j%, s1&, s2&, s3&
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("期中").Range("a1").CurrentRegion
- brr = Sheets("期末").Range("a1").CurrentRegion
- ReDim ar(1 To UBound(arr), 1 To UBound(arr, 2) * 2 + 1)
- ReDim br(1 To UBound(arr), 1 To UBound(arr, 2))
- ReDim cr(1 To UBound(brr), 1 To UBound(brr, 2))
- For i = 3 To UBound(arr)
- d(arr(i, 1)) = i
- Next
- For i = 3 To UBound(brr)
- If Not d.exists(brr(i, 1)) Then
- s3 = s3 + 1
- For j = 1 To UBound(brr, 2)
- cr(s3, j) = brr(i, j)
- Next
- d(brr(i, 1)) = "," & i
- Else
- s1 = s1 + 1
- n = d(brr(i, 1))
- For j = 1 To UBound(arr)
- ar(s1, j) = arr(n, j)
- ar(s1, j + 6) = brr(i, j)
- Next
- d(brr(i, 1)) = d(brr(i, 1)) & "," & i
- End If
- Next
- b = d.items
- For i = 0 To d.Count - 1
- If InStr(b(i), ",") = 0 Then
- s2 = s2 + 1
- For j = 1 To UBound(arr, 2)
- br(s2, j) = arr(b(i), j)
- Next
- End If
- Next
- Sheet1.Range("a4").Resize(s1, UBound(ar, 2)) = ar
- Sheet2.Range("a4").Resize(s2, UBound(br, 2)) = br
- Sheet3.Range("a4").Resize(s3, UBound(cr, 2)) = cr
- End Sub
复制代码 |
|