|
前面列号做个变量,把里面的常量替换掉,附件供参考。
Sub test()
Dim arr, ar, i, j, m, n
Dim d: Set d = CreateObject("scripting.dictionary")
Dim d1: Set d1 = CreateObject("scripting.dictionary")
With Sheets("数据")
ar = Array("工号", "机号", "天数")
For i = 0 To 2
ar(i) = .Range("a1:bb1").Find(ar(i)).Column
Next
n = .Cells(Rows.Count, ar(0)).End(xlUp).Row
arr = .Range("a1:bb" & n)
End With
For i = 2 To UBound(arr)
If arr(i, ar(2)) >= 26 Then
m = m + 1: d1(arr(i, ar(0))) = d1(arr(i, ar(0))) + 1
For j = 1 To 3
arr(m, j) = arr(i, ar(j - 1))
Next
arr(m, 4) = d1(arr(i, ar(0)))
Else
If Not d.exists(arr(i, ar(0))) And arr(i, ar(2)) < 26 Then
m = m + 1: d(arr(i, ar(0))) = m: d1(arr(i, ar(0))) = d1(arr(i, ar(0))) + 1
For j = 1 To 3
arr(m, j) = arr(i, ar(j - 1))
Next
arr(m, 4) = d1(arr(i, ar(0)))
Else
arr(d(arr(i, ar(0))), 2) = arr(d(arr(i, ar(0))), 2) & "," & arr(i, ar(1))
arr(d(arr(i, ar(0))), 3) = arr(d(arr(i, ar(0))), 3) + arr(i, ar(2))
End If
End If
Next
With Sheets("汇总")
.Range("A2").CurrentRegion.Offset(1, 0).ClearContents
.Range("a2").Resize(m, 4) = arr
End With
End Sub |
|