Microsoft Office Forums - View Single Post

Colin Legg · 05-31-2012, 09:20 AM

There's a limit of 64 sortfields to a sort which is one of several reasons why you might get that error (you're trying to create 365 sortfields). I've guessed at what you want to do and tidied your code to the below. If it's not what you want please can you describe in simple words what you want your code to do?

Code:

 
Sub SortDaysProvider()
    Dim DayRange As Long
    Dim TopRow As Long
    Dim sRange As Range
    Dim fRange As Range
 
    Application.ScreenUpdating = False
 
    For DayRange = 1 To 365
        TopRow = (DayRange * 17) + 9
 
        With ActiveWorkbook.Worksheets("Input Calendar (2012)")
 
            Set sRange = .Range("E" & TopRow & ":" & "BN" & TopRow + 14)
            Set fRange = .Range("E" & TopRow & ":" & "E" & TopRow + 14)
 
            With .Sort
                .SortFields.Add _
                     Key:=fRange, _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortNormal
                 .SetRange sRange
                 .Header = xlGuess
                 .MatchCase = False
                 .Orientation = xlTopToBottom
                 .SortMethod = xlPinYin
                 .Apply
                 .SortFields.Clear
            End With
        End With
    Next DayRange
 
    Application.ScreenUpdating = True
End Sub