|
- Sub test2()
- Dim r%, i%, j%, m%, n%
- Dim arr, brr
- Dim reg As New RegExp
- With reg
- .Global = True
- .Pattern = "([^\d]+)(\d+)"
- End With
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:a" & r)
- ReDim brr(1 To UBound(arr), 1 To 1)
- For i = 1 To UBound(arr)
- Set mh = reg.Execute(arr(i, 1))
- If mh.Count > 0 Then
- ss = mh(0).SubMatches(0)
- s = 0
- For j = 1 To Len(ss)
- ch = Mid(ss, j, 1)
- ii = Asc(ch)
- s = s + ii * Application.Power(10, 10 - j)
- Next
- brr(i, 1) = s + Val(mh(0).SubMatches(1))
- End If
- Next
- End With
- For i = 1 To UBound(brr) - 1
- p = i
- For j = i + 1 To UBound(brr)
- If brr(p, 1) > brr(j, 1) Then
- p = j
- End If
- Next
- If p <> i Then
- temp = brr(i, 1)
- brr(i, 1) = brr(p, 1)
- brr(p, 1) = temp
- temp = arr(i, 1)
- arr(i, 1) = arr(p, 1)
- arr(p, 1) = temp
- End If
- Next
- With Worksheets("sheet1")
- .Range("b2").Resize(UBound(arr), 1) = arr
- End With
- End Sub
复制代码 |
|