|
发表于 2015-5-8 10:41
|
显示全部楼层
本楼为最佳答案
- Sub grf()
- '第2列工号,第3列姓名,第8列制单号,第11列工序名称,第14列产量!
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- arr = Sheets("交飞明细").Range("A1").CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 100)
- For i = 2 To UBound(arr)
- x = arr(i, 2): cl = arr(i, 14)
- zf = arr(i, 8) & " " & arr(i, 11) & " " & cl '工号+工序名称+产量
- If Not d.Exists(x) Then
- s = s + 1
- d(x) = s
- brr(s, 1) = arr(i, 2)
- brr(s, 2) = arr(i, 3)
- brr(s, 3) = cl '产量
- brr(s, 4) = zf '工号+工序名称+产量
- Else
- n = d(x)
- brr(n, 3) = brr(n, 3) + cl
- brr(n, 4) = brr(n, 4) & "," & zf '先把所有 工号+工序名称+产量 全部放入第4列
- End If
- Next
- d.RemoveAll
-
- For i = 1 To s
- xrr = Split(brr(i, 4), ",") '对第4列进行分析
- For j = 0 To UBound(xrr)
- y = xrr(j)
- yrr = Split(y, " ")
- gx = yrr(1) '工序名称
- If Not d.Exists(gx) Then
- d(gx) = Val(yrr(2)) '产量
- d1(gx) = yrr(0) '工号
- Else
- d(gx) = d(gx) + Val(yrr(2))
- If InStr(d1(gx), yrr(0)) = 0 Then d1(gx) = d1(gx) & "/" & yrr(0)
- End If
- Next
- For Each gx In d.Keys
- If InStr(d1(gx), "/") > 0 Then d1(gx) = "(" & d1(gx) & ")" '如果有"/",就加上括号
- k = k + 1: If 3 + k > maxk Then maxk = 3 + k '最大列
- brr(i, 3 + k) = d1(gx) & " " & gx & " " & d(gx) & "件"
- Next
- d.RemoveAll
- d1.RemoveAll
- k = 0
- Next
- [a14].Resize(s, maxk) = brr
- End Sub
复制代码 |
|