- Sub Macro1()
- Dim arr, brr(1 To 20000, 1 To 5), d, i&, s&
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- For i = 3 To UBound(arr)
- If arr(i, 4) = "" Then arr(i, 4) = arr(i - 1, 4)
- Set rng = Sheet1.Cells(i, "k").Resize(1, 10).Find("*")
- If Not rng Is Nothing Then
- If Not d.exists(arr(i, 4)) Then
- s = s + 1
- d(arr(i, 4)) = s
- brr(s, 1) = arr(i, 1)
- brr(s, 2) = arr(i, 4)
- brr(s, 3) = rng
- brr(s, 4) = arr(i, 21)
- brr(s, 5) = arr(i, 22)
- Else
- brr(d(arr(i, 4)), 3) = brr(d(arr(i, 4)), 3) & "/" & rng
- If arr(i, 21) <> "" Then
- brr(d(arr(i, 4)), 4) = brr(d(arr(i, 4)), 4) & "/" & arr(i, 21)
- brr(d(arr(i, 4)), 5) = brr(d(arr(i, 4)), 5) & "/" & arr(i, 22)
- End If
- End If
- End If
- Next
- Sheet2.Activate
- Range("a2").Resize(s, 5) = brr
- End Sub
复制代码 |