|
楼主 |
发表于 2009-7-5 08:50
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
zhaogang1960 看了代码,有点跟不上思路,能帮我注释一下.谢谢
Sub Macro1()
Dim arr, brr(), d As Object, dic As Object, i As Long, j As Integer, m As Integer
arr = Range("L2:O" & Range("IV2").End(xlToLeft).Column)
Set d = CreateObject("Scripting.Dictionary")
For j = 3 To UBound(arr, 2)
d(arr(1, 1) & arr(1, j)) = ""
Next
arr = Range("A2:I" & Range("A65536").End(xlUp).Row)
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(arr)
If d.exists(arr(i, 1) & arr(i, 2)) Then
If dic(arr(i, 1) & arr(i, 3) & arr(i, 4)) = "" Then
m = m + 1
dic(arr(i, 1) & arr(i, 3) & arr(i, 4)) = m
ReDim Preserve brr(1 To 4, 1 To m)
For j = 2 To 4
brr(j - 1, m) = arr(i, j)
Next
brr(4, m) = arr(i, 9)
Else
brr(4, dic(arr(i, 1) & arr(i, 3) & arr(i, 4))) = brr(4, dic(arr(i, 1) & arr(i, 3) & arr(i, 4))) + arr(i, 9)
End If
End If
Next
If m > 0 Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("K4:N" & Range("l65536").End(xlUp).Row + 1).Clear
With [k4].Resize(m, 4)
.Value = WorksheetFunction.Transpose(brr)
' .Sort Key1:=Range("K4").Resize(m)'如果颜色列出现不连续,启用这一句
.Borders.LineStyle = xlContinuous
End With
arr = Range("K4").Resize(m)
For i = 2 To UBound(arr)
If arr(i, 1) = arr(i - 1, 1) Then Cells(i + 2, 11).Resize(2).Merge
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub |
|