Excel VBA : Sort Sheets in Alphanumeric Order

Question

我有一个大约 30 张的工作簿，我试图按字母数字顺序排列。例如:“纽约 9 号、纽约 10 号、纽约 11 号”
我的代码无法在一位数之后订购两位数。 “10、11、9”
有没有人熟悉计算这个的方法？非常感谢!

Sub AscendingSortOfWorksheets()

'Sort worksheets in a workbook in ascending order
Dim SCount, i, j As Integer


Application.ScreenUpdating = False


SCount = Worksheets.Count

For i = 1 To SCount - 1

    For j = i + 1 To SCount
        
        If Worksheets(j).Name < Worksheets(i).Name Then
            Worksheets(j).Move before:=Worksheets(i)
        End If
    
    Next j
Next i


End Sub

Answer 1

如评论中所述，您需要用零填充数字，在您的情况下，单个数字需要用 1 个零填充。使用此功能

Function PadNumber(sName As String, lNumOfDigits As Long) As String
    Dim v As Variant
    Dim vPrefixList As Variant
    Dim sTemp As String
    Dim i As Long
    
    ' Add all other possible prefixes in this array
    vPrefixList = Array("New York")
    
    sTemp = sName
    
    For Each v In vPrefixList
        sTemp = Replace(LCase(sTemp), LCase(v), "")
    Next v
    
    sTemp = Trim(sTemp)
    PadNumber = sTemp
    
    For i = Len(sTemp) + 1 To lNumOfDigits
        PadNumber = "0" & PadNumber
    Next i
    
    PadNumber = Replace(sName, sTemp, PadNumber)
    
End Function

然后更改行 If Worksheets(j).Name < Worksheets(i).Name Then至

If PadNumber(LCase(Worksheets(j).Name), 2) < PadNumber(LCase(Worksheets(i).Name), 2) Then

备注 我添加了 LCase在比较中。在这种特殊情况下，区分大小写可能对您无关紧要，但您始终需要牢记这一点。