LISTING FILES IN FOLDER - INCORRECT ORDER

[Paul_Hossler]

Possilble technique that ou can integrate into your code

Pretty brute force and simplistic

Capture.JPG

Option Explicit


Sub Renumber()
    Dim i As Long, j As Long
    Dim A() As String
    Dim r As Range
    Dim s As String, s2 As String
    
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    
    ReDim A(1 To r.Rows.Count, 1 To 2)
        
    For i = 1 To r.Rows.Count
        s = UCase(r.Cells(i, 1).Text)
        A(i, 1) = r.Cells(i, 1).Text
        
        If s Like "*###.PDF" Then
            A(i, 2) = Right(s, 7)
        ElseIf s Like "*##.PDF" Then
            A(i, 2) = "0" & Right(s, 6)
        ElseIf s Like "*#.PDF" Then
            A(i, 2) = "00" & Right(s, 5)
        End If
    Next i
    
    'simple bubble sort - could put onto worksheet and sort
    For i = LBound(A) To UBound(A, 1) - 1
        For j = i + 1 To UBound(A, 1)
            If A(i, 2) > A(j, 2) Then
                s = A(i, 1)
                s2 = A(i, 2)
                A(i, 1) = A(j, 1)
                A(i, 2) = A(j, 2)
                A(j, 1) = s
                A(j, 2) = s2
            End If
        Next j
    Next i
    
    'put back - replace with
    For i = LBound(A, 1) To UBound(A, 1)
        ActiveSheet.Cells(i, 4).Value = A(i, 1)
    Next i
End Sub