VBA sort not working properly when sorting on values that are produced by formula

Question

I have a sheet of values that I want to sort with VBA. I want to sort them in descending (reverse alphabetical) order by a particular header. My sheet looks like this:

   A            B            C
1  Date Opened  Date Closed  Status
2  07/12/17     07/15/17     closed
3  07/16/17                  open

The value of column C is calculated, so that if there's nothing in the "Date Closed" column, the status is set to "open" automatically.

I want to use VBA to sort the table by Column C in reverse alphabetical order, so that open tickets appear before closed tickets. This is the VBA I'm trying to use:

Sub Sort_Status()
    With ActiveSheet.Sort
        .SetRange Range("A1:C3")
        .SortFields.Add Key:=Range("C2:C3"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Header = xlYes
        .Apply
    End With
End Sub

If I run this on my data table with the formula in it, I get the error: 1004: The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

However, if I paste these values into a new sheet (e.g. so that column C has just the values "closed" or "open", but not the formula), the same code works without errors.

I can also manually perform a sort in Excel on the sheet with the formulas, and it works fine there.

Other troubleshooting steps I've tried:

Another issue had the same error code, and it was due to calling the subroutine from another sheet. However, this subroutine is simply in Modules and isn't being called from another sheet.
Checked through online tutorials like this and this and tried using their code to see if I had the same issue

Step 1 would be, obviously, just record a macro of doing it manually, and modify the macro if needed :) — David Zemens
– David Zemens, Commented Jul 19, 2018 at 17:23
Yeah, that's actually where I started and I've been stripping down the code from there. It works with a recorded version but something's going wrong somewhere. — elegant-solutions
– elegant-solutions, Commented Jul 19, 2018 at 17:27
You might want to do .SortFields.Clear first inside your With block. I'm not sure that will fix it, but it would get rid of any previously existing sort fields, which might've been specified incorrectly. — David Zemens
– David Zemens, Commented Jul 19, 2018 at 17:31
Must've come from previous debugging attempts. The rules are added sequentially and the error won't raise until/unless you try to .Apply in which case a single bad rule will cause that error. — David Zemens
– David Zemens, Commented Jul 19, 2018 at 17:36

David Zemens · Accepted Answer · 2018-07-19 17:38:53Z

2

I've never liked working with the ActiveSheet.Sort, preferring the more explicit Range.Sort. This seems to work for me:

Range("A1:C3").Sort Range("C1"), xlDescending, Header:=xlYes

Also: You might want to do .SortFields.Clear first inside your With block. I'm not sure that will fix it, but it would get rid of any previously existing sort fields, which might've been specified incorrectly.

answered Jul 19, 2018 at 17:38

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA sort not working properly when sorting on values that are produced by formula

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related