|
- 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, 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) = arr(i, 5)
- brr(s, 4) = cl '产量
- brr(s, 5) = zf '工号+工序名称+产量
- Else
- n = d(x)
- brr(n, 4) = brr(n, 4) + cl
- brr(n, 5) = brr(n, 5) & "," & zf '先把所有 工号+工序名称+产量 全部放入第4列
- End If
- Next
- d.RemoveAll
-
- For i = 1 To s
- xrr = Split(brr(i, 5), ",") '对第4列进行分析
- For j = 0 To UBound(xrr)
- y = xrr(j)
- yrr = Split(y, " ")
- gx = yrr(0) '工序名称
- d(gx) = d(gx) + Val(yrr(1))
- Next
- For Each gx In d.Keys
- k = k + 1: If 4 + k > maxk Then maxk = 4 + k '最大列
- brr(i, 4 + k) = gx & " " & d(gx) & " 件"
- Next
- d.RemoveAll
- k = 0
- Next
- Sheet3.[a2].Resize(s, maxk) = brr
- End Sub
复制代码 |
|