时间戳

首页 > 解决方案 > Why do I get a 1004 error when adding a key using SortFields.Add2?

excel - Why do I get a 1004 error when adding a key using SortFields.Add2?

问题描述

I am trying to sort the data in an Excel table for use within a UserForm. I have the variable holding the Listobject declared for the whole module, and I set it on initialization. However, when I try to apply the sort field keys, it throws the following error:

Run-time error '1004':

Application-defined or object-defined error

Here are the relevant code portions:

Private lotable_OVERLAY_DETAILS As ListObject

Private Sub UserForm_Initialize()

    Set lotable_OVERLAY_DETAILS = OVERLAY_DETAILS.ListObjects("OVERLAY_DETAILS")
    Call Sort_OVERLAY_DETAILS

End Sub

Private Sub Sort_OVERLAY_DETAILS()

    lotable_OVERLAY_DETAILS.ShowAutoFilter = False 'Clear any existing filters
    lotable_OVERLAY_DETAILS.ShowAutoFilter = True

    lotable_OVERLAY_DETAILS. _
        Sort.SortFields.Add2 key:=Range("OVERLAY_DETAILS[[#All],[PORTFOLIO_NAME]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    lotable_OVERLAY_DETAILS. _
        Sort.SortFields.Add2 key:=Range("OVERLAY_DETAILS[[#All],[OVERLAY_NAME]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With lotable_OVERLAY_DETAILS.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

The error occurs when trying to execute either of the lotable_OVERLAY_DETAILS.Sort.SortFields.Add2lines. This code was working not too long ago, and there are several other tables that I sort using the same method. I'm not sure why I am getting this error only on this one now. What am I missing?

标签： excelvbasorting

解决方案

I solved this problem by adding the following before the SortFields lines:

lotable_OVERLAY_DETAILS.Sort.SortFields.Clear

Turns out the tables have a 64 sort field limit on them, and they persist even when clearing the AutoFilters.

In my case, this error popped up because I was testing the code over and over. As I did so, this particular line added two SortFields and my other ones only added one. Therefore, this table hit the limit sooner and through the error. Clearing the filters out before adding new ones solved all the problems.

推荐阅读