CODEWITHSUNDEEP
excelvbaexcel-2016
eirikdaude
eirikdaude

Reputation: 3255

Unexpected results from custom sort using VBA

I am trying to sort a table in my spreadsheet, using a custom sort. After a bit back and forth, I came up with this macro:

Sub sort_SI()
    With Resultat_SI.Sort
        .SortFields.Clear
        .SetRange Resultat_SI.Range(Resultat_SI.Range("A1"), Resultat_SI.Range("J" & Resultat_SI.Rows.Count).End(xlUp))
        .SortFields.Add Key:=Resultat_SI.Columns("J"), CustomOrder:=Join(Application.Transpose(Fargar.ListObjects("Fargekoder").ListColumns(3).DataBodyRange), ",")
        .Header = xlYes
        .Apply
    End With
End Sub

It runs just fine, and the order of my entries does change, after the sub is finished, but there seems no sense to the ordering. At the very least I would expect the lines with the same values in column J to be grouped together, but this does not seem to be the case.

Printing Sort.SetRangeto the immediate window shows that it is

as expected, while Join(Application.Transpose(Fargar.ListObjects("Fargekoder").ListColumns(3).DataBodyRange), ",") gives

also as expected.

The two sheets I am referencing in the sub looks like this:

Resultat_SI

enter image description here

Fargekoder

enter image description here

Basically I want my table in Resultat_SI to be sorted in the same order as the colors appear in the table in Fargekoder, with blank results appearing at the bottom, but I am failing miserably at achieving this result. Can someone please tell me where I am going wrong?

Upvotes: 0

Views: 46

Answers (1)

eirikdaude
eirikdaude

Reputation: 3255

The issue with sorting my table was not with the sorting code itself, but rather with the formulas used to populate the table. I had locked the cell references in the formulas, meaning that once a line had been sorted to a different location in the table, the formulas on that line still referenced whatever was now shown to its previous location.

In other words, all it took to fix the problem was removing one $ from each cell reference, in order to have them change to the appropriate cells after having been sorted.

I.e. changing

  • =IFERROR(VLOOKUP($H$2;Fargekoder;3;FALSE);"")

to

  • =IFERROR(VLOOKUP($H2;Fargekoder;3;FALSE);"")

was all it took.

Upvotes: 1

Related Questions