|
- Sub 分包人汇总()
- Dim arr, brr, crr, d, i, j, r, n
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet2.UsedRange
- With Sheet1
- r = .Cells(Rows.Count, 2).End(xlUp).Row
- brr = .Range("a2:g" & r)
- End With
- ReDim crr(1 To UBound(brr), 1 To 6)
- For i = 2 To UBound(brr)
- For j = 2 To UBound(arr)
- If InStr(brr(i, 2), arr(j, 1)) Then
- sa = arr(j, 1)
- If Not d.exists(sa) Then
- n = n + 1
- crr(n, 1) = Split(brr(i, 2), "")(0)
- crr(n, 2) = arr(j, 1)
- crr(n, 3) = brr(i, 4)
- d(sa) = n
- Else
- m = d(sa)
- crr(m, 3) = crr(m, 3) + brr(i, 4)
- End If
- End If
- Next
- Next
- Stop
- With Sheet5
- .UsedRange.Offset(1).Clear
- .[b2].Resize(n, 6) = crr
- End With
- End Sub
复制代码 |
|