Sorting worksheet values in Excel 2007 VBA

[phudgens]

I have a vague recollection that Excel VBA does not do sorting. Is that why when I run the following code:

For i = 0 To iTotalZones
    
    iOffset = i * 3
    With Workbooks(ThisWkBk).Worksheets("Operators")
         Set SortRange = .Range(.[A2].Offset(0, iOffset + 1), .[A2].Offset(0, iOffset + 2).End(xlDown))
    End With
    
    SortRange.Activate
    SortRange.Select
    
    ActiveWorkbook.Worksheets("Operators").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Operators").Sort.SortFields.Add Key:= _
        SortRange, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Operators").Sort
        .SetRange SortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Next i

I get the message: "The sort reference is not valid. Make sure that it's in the data you want to sort, and the first SortBy box isn't the same or blank". That message occurs at the ".Apply". I'm not aware that there is supposed to be a SortBy box. I can't find any information in Help on how to use it.

Thanks for any help,
Paul Hudgens
Denver

 

[Gruuuu]

This code looks a bit overcomplicated. One thing I find odd is that it's using SortRange as a Key. SortRange is an array of cells, Key only needs 1 range reference.

I've always just defined a range to a variable, and used the Range.Sort method.

This works Fine-n-Dandy for me in Excel 2007

Sub hithere()
iTotalZones = 3

ThisWkBk = ActiveWorkbook.Name
For i = 0 To iTotalZones
    
    iOffset = i * 3
    With Workbooks(ThisWkBk).Worksheets(1)
         .Range(.[A2].Offset(0, iOffset + 1), .[A2].Offset(0, iOffset + 2).End(xlDown)) _
         .Sort Key1:=.[A2].Offset(0, iOffset + 1), Order1:=xlDescending, Header:=xlYes
    End With
    
Next i
End Sub

 

[SkipVought]

Turn on your macro recorder and RECORD a sort.

Modify your recorded code as required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

You are correct - I had actually posted a reply to my own post in which I noted my mistake. The examples I had found used the same range as was being sorted for the key. As I thought about it, that didn't make sense - I specified the key as the cell value that it needed to be and it worked. Thanks.

Paul Hudgens
Denver

 

[Gruuuu]

Indeed I see that the Excel 2007 Sort method that is referenced in the VBA help file shows exactly what you tried. It doesn't make sense to me. But maybe I'll check it out later.