获取指定内容后再进行排序:
- Sub test()
- Dim k%, arr(1 To 10000, 1 To 2)
- Set fso = CreateObject("scripting.filesystemobject")
- Set sh = ActiveSheet
- Application.ScreenUpdating = False
- sh.Range("A2:B" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row + 1).ClearContents
- For Each f In fso.getfolder(ThisWorkbook.Path).Files
- If InStr(f.Name, ".txt") <> 0 Then
- k = k + 1
- arr(k, 1) = k: arr(k, 2) = Split(f.Name, "_")(1)
- End If
- Next f
- For i = 1 To k - 1
- For j = i + 1 To k
- s1 = Mid(arr(i, 2), 2, InStr(arr(i, 2), "~") - 2)
- s2 = Mid(arr(j, 2), 2, InStr(arr(j, 2), "~") - 2)
- If s1 = s2 Then
- s1 = Right(Len(arr(i, 2)), Len(arr(i, 2)) - InStr(arr(i, 2), "~") - 1)
- s2 = Right(Len(arr(j, 2)), Len(arr(j, 2)) - InStr(arr(j, 2), "~") - 1)
- End If
- If InStr(s1, "-") > 0 And InStr(s2, "-") > 0 Then
- s1 = Val(Replace(s1, "-", ""))
- s2 = Val(Replace(s2, "-", ""))
- ElseIf InStr(s1, "-") > 0 And InStr(s2, "-") = 0 Then
- If Left(s1, 1) = Left(s2, 1) And Val(s1) = Val(s2) Then
- s1 = Val(Replace(s1, "-", ""))
- s2 = Val(s2)
- Else
- s1 = Val(s1)
- s2 = Val(s2)
- End If
- ElseIf InStr(s1, "-") = 0 And InStr(s2, "-") > 0 Then
- If Left(s1, 1) = Left(s2, 1) And Val(s1) = Val(s2) Then
- s2 = Val(Replace(s2, "-", ""))
- s1 = Val(s1)
- Else
- s1 = Val(s1)
- s2 = Val(s2)
- End If
- Else
- s1 = Val(s1)
- s2 = Val(s2)
- End If
- If s1 > s2 Then
- temp = arr(i, 2): arr(i, 2) = arr(j, 2): arr(j, 2) = temp
- End If
- Next
- Next
- sh.[A2].Resize(k, 2) = arr
- Application.ScreenUpdating = True
- End Sub
复制代码
|