|
- Sub aaa()
- Dim reg, n, st1, st2, arr, i&
- n = Range("e65536").End(3).Row
- If n < 5 Then Exit Sub
- n = n + 3 - Val(Right(Range("e" & n), 1))
- arr = Range("e5:e" & n)
- Set reg = CreateObject("vbscript.regexp")
- With reg
- '全局匹配
- .Global = True
- '匹配规则
- .Pattern = "\d+($|\n)"
- For i = 1 To UBound(arr)
- If arr(i, 1) <> "" Then
- '替换
- st1 = .Replace(arr(i, 1), "")
- '匹配
- st2 = .Execute(arr(i, 1))(0)
- '把匹配到数字提取出来
- n = Val(st2)
- Else
- '对于空行,会把上一个非空单元格的数字提取出来加1,然后重新组合填入
- n = n + 1
- arr(i, 1) = st1 & Format(n, WorksheetFunction.Rept("0", Len(st2)))
- End If
- Next
- End With
- [f5].Resize(UBound(arr)) = arr
- End Sub
复制代码 |
|