|
- Sub tt()
- arr = [g10].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- x = [h65536].End(3) 'H列最下面数据
- s = Left(x, 3) '前三位字母:XYZ
- a = Val(Mid(x, 4, 3)) '中三位值:629
- For i = 2 To UBound(arr)
- If Len(arr(i, 2)) = 0 Then '去掉头上已填充数据的单元格
- x = arr(i, 1)
- If Not d.exists(x) Then k = k + 1: d1(x) = a + k
- d(x) = d(x) + 1
- arr(i, 2) = s & d1(x) & d(x)
- End If
- Next
- For i = 2 To UBound(arr)
- x = arr(i, 1)
- If d(x) = 1 Then Mid(arr(i, 2), Len(arr(i, 2)), 1) = "0"
- Next
- [g10].CurrentRegion = arr
- End Sub
复制代码 |
|