|
发表于 2016-3-7 14:01
|
显示全部楼层
本楼为最佳答案
- Sub 匹配()
- arr = Sheet1.[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr) '读入数据并分类
- zr = arr(i, 15) '主任
- zz = arr(i, 14) '组长
- zw = arr(i, 10) '职位
- xm = arr(i, 2) '姓名
- bm = arr(i, 16) '部门
- If Len(zr) > 0 And Len(zz) > 0 Then
- If InStr(d(zr), zz) = 0 Then d(zr) = d(zr) & "," & zz
- End If
- If Len(zz) > 0 Then
- x = zz & zw
- If zw = "半烫" Or zw = "装篮工" Or zw = "组长助理" Then
- d(x) = xm
- ElseIf zw = "车工" Then
- d(zz) = d(zz) & "," & xm
- End If
- End If
- xx = ""
- If zw = "飞机位" Then xx = zw
- If bm = "裁床组" Or bm = "后道组" Then xx = Left(bm, 2)
- If zw = "车工" And Len(zz) = 0 Then xx = "未分组"
- If Len(xx) > 0 Then d(xx) = d(xx) & "," & xm
- Next
- With Sheet2
- .[c3:be21] = ""
- For c = 3 To 43 Step 10
- zr = .Cells(2, c)
- If d.exists(zr) Then
- xrr = Split(Mid(d(zr), 2), ",")
- .Cells(3, c).Resize(1, UBound(xrr) + 1) = xrr '填入组长
- End If
- Next
- brr = .Range("a1:be21")
- For c = 3 To 52
- zz = .Cells(3, c) '组长
- For i = 4 To 6 '填入组长对应的半烫、装篮工、组长助理
- x = zz & .Cells(i, 2)
- .Cells(4, c) = d(x)
- Next
- If d.exists(zz) Then '填入组长对应的车工
- xrr = Split(Mid(d(zz), 2), ",")
- .Cells(5, c).Resize(UBound(xrr) + 1, 1) = Application.Transpose(xrr)
- End If
- Next
- For c = 53 To 56 '填入飞机位、未分组、裁床、后道
- xx = .Cells(2, c)
- If d.exists(xx) Then
- xrr = Split(Mid(d(xx), 2), ",")
- .Cells(3, c).Resize(UBound(xrr) + 1, 1) = Application.Transpose(xrr)
- End If
- Next
- End With
- End Sub
复制代码 |
|