|
- Sub Macro1()
- Dim arr, brr, d, i&, j%
- Set d = CreateObject("scripting.dictionary")
- arr = Range("t10:t" & Range("t65536").End(xlUp).Row)
- ReDim brr(1 To UBound(arr) - 1, 1 To 1)
- For i = 2 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) & "," & i
- Next
- For i = 2 To UBound(arr)
- sj = arr(i, 1)
- If sj <> "" And d.exists(sj) Then
- x = Split(Mid(d(sj), 2), ",")
- For j = 0 To UBound(x)
- s = s + 1
- brr(s, 1) = sj
- arr(x(j), 1) = ""
- Next
- If InStr(sj, "-") Then
- y = Split(sj, "-")
- zf = y(1) & "-" & y(0)
- Else
- With CreateObject("vbscript.regexp")
- .Global = True
- .Pattern = "[A-Z]\d*"
- Set m = .Execute(sj): zf = ""
- For j = m.Count - 1 To 0 Step -1
- zf = zf & m(j)
- Next
- End With
- End If
- If d.exists(zf) Then
- y = Split(Mid(d(zf), 2), ",")
- For j = 0 To UBound(y)
- s = s + 1
- brr(s, 1) = zf
- arr(y(j), 1) = ""
- Next
- End If
- End If
- Next
- Range("v11").Resize(UBound(brr)) = brr
- End Sub
复制代码 |
|