请问遍历文件后的排序问题

yongly1211

'第1个自定义函数：提取小括号内数字
Public Function get_num_between_bracket(sr As String) As Double
Dim reg As New RegExp
With reg
    .Global = False
    .Pattern = "\(\d+"
    Set matc = .Execute(sr)
End With
get_num_between_bracket = CDbl(Replace(matc.Item(0).Value, "(", ""))
End Function

'第2个自定义函数：二维数组按值大小排序

Public Function sort_by_num_2col(arr)
Dim x&, y&, temp&, temp2&
'对特定数值进行排序即可,可使用选择法、冒泡法、插入法，这里是插入法
For x = 1 + 1 To UBound(arr)
    temp = arr(x, 1)
    temp2 = arr(x, 2)
    For y = x - 1 To 1 Step -1
        If arr(y, 1) <= temp Then Exit For
        arr(y + 1, 1) = arr(y, 1)
        arr(y + 1, 2) = arr(y, 2)
    Next y
    arr(y + 1, 1) = temp
    arr(y + 1, 2) = temp2
Next x
sort_by_num_2col = arr
End Function


'3、测试程序
Sub model()
Dim arr(), brr(), sort, res()
Dim h&, Row&
Row = Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To Row - 1)
ReDim brr(1 To Row - 1, 1 To 2)
ReDim sort(1 To Row - 1)
ReDim res(1 To Row - 1)
arr = Application.Transpose(Range("A2:A" & Row))
For h = 1 To Row - 1
    brr(h, 1) = get_num_between_bracket(CStr(arr(h))) '待排序数组
    brr(h, 2) = h   '映射arr
Next h
sort = sort_by_num_2col(brr) '排序完成，根据第2列顺序提取
For h = 1 To Row - 1
    res(h) = arr(sort(h, 2))
Next h
Range("C2:C" & Row) = Application.Transpose(res)
MsgBox "ok"
End Sub

截个图：

jimingcheung

yongly1211 发表于 2020-5-25 13:09
'第1个自定义函数：提取小括号内数字
Public Function get_num_between_bracket(sr As String) As Double
...

太感谢了，亲测已成功，我也学到了许多。第二个自定义函数我没有看懂，我用的是冒泡排序。