|
本帖最后由 fjmxwrs 于 2011-12-14 14:55 编辑
jmhwjd 发表于 2011-12-13 14:45
好的!但愿有高手能帮忙!
完成了,你可以增加数据和表格试下,学科也可以增加
- Sub FuiZon()
- Dim d As Object, d1 As Object
- Dim Arr, i&, j&, j1&, k&, k1&, S$
- Dim iSheet As Worksheet
- k1 = 1
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- Range("A1").CurrentRegion.ClearContents
- For Each iSheet In Worksheets
- If iSheet.Name <> "汇总" Then
- With iSheet
- Arr = .UsedRange
- End With
- For j = 1 To UBound(Arr, 2)
- If Not d.Exists(Arr(1, j)) Then
- k = k + 1
- d(Arr(1, j)) = k
- Cells(1, d(Arr(1, j))) = Arr(1, j)
- End If
- Next j
- For i = 2 To UBound(Arr)
- S = Arr(i, 1) & Arr(i, 2)
- If Not d1.Exists(S) Then
- k1 = k1 + 1
- d1(S) = k1
- Cells(d1(S), 1) = Arr(i, 1)
- Cells(d1(S), 2) = Arr(i, 2)
- For j1 = 3 To UBound(Arr, 2)
- Cells(d1(S), d(Arr(1, j1))) = Arr(i, j1)
- Next j1
- Else
- For j1 = 3 To UBound(Arr, 2)
- Cells(d1(S), d(Arr(1, j1))) = Arr(i, j1)
- Next j1
- End If
- Next i
- Erase Arr
- End If
- Next iSheet
- Range("A1").CurrentRegion.Sort Range("a1"), xlAscending, Header:=xlYes
- Application.ScreenUpdating = True
- End Sub
复制代码
新建 Microsoft Excel 工作表 (2)(1).zip
(14.51 KB, 下载次数: 7)
|
|