|
- Sub Macro1()
- On Error Resume Next
- Dim arr, brr, crr, cr, d, d2, i&, s%, n&
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- brr = Sheet2.Range("a1").CurrentRegion
- crr = Sheet3.Range("a1").CurrentRegion
- ReDim cr(1 To UBound(crr) - 1, 1 To 2)
- For i = 2 To UBound(arr)
- If arr(i, 2) = "" Then arr(i, 2) = arr(i - 2, 2)
- d(arr(i, 3)) = arr(i, 2) & arr(i, 4)
- Next
- For i = 2 To UBound(brr)
- d2(brr(i, 1)) = d2(brr(i, 1)) + 1
- Next
- For i = 2 To UBound(crr)
- If Not d2.exists(crr(i, 1)) Then s = 1 Else s = d2(crr(i, 1)) + 1
- cr(i - 1, 1) = crr(i, 1)
- cr(i - 1, 2) = d(crr(i, 1)) & Format(s, "00")
- Next
- n = Sheet2.Range("a65536").End(xlUp).Row + 1
- Sheet2.Cells(n, 1).Resize(UBound(cr), 2) = cr
- Sheet3.Range("a1").CurrentRegion.Offset(1, 0).ClearContents
- End Sub
复制代码 |
|