Thread: Convert Sort code for Excel 2003

Hi All, I have the following macro working in Excel 2007 but it appears not to be compatible with 2003:

Sub VT_Filter()
Sheets("Dashboard").Calculate

Dim ws As Worksheet
Dim Str_Ce1, Str_Ce2 As String
Dim Byt_j As Byte

Set ws = Sheets("CHART DATA")
  With ws
    .Range("$A$8:$T$305").AutoFilter
    
     For Byt_j = 1 To 4
                
        Str_Ce1 = Choose(Byt_j, "9", "52", "123", "208")
        Str_Ce2 = Choose(Byt_j, "50", "121", "206", "305")
        
        ws.Sort.SortFields.Clear
            ws.Sort.SortFields.Add Key:=Range("B" & Str_Ce1 & ":B" & Str_Ce2), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            'SORT BY PCT
         
            ws.Sort.SortFields.Add Key:=Range("E" & Str_Ce1 & ":E" & Str_Ce2), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            'THEN SORT BY QUALIFIER
         
         With ws.Sort
         
             .SetRange Range("A" & Str_Ce1 & ":T" & Str_Ce2)
             .Header = xlGuess
             .MatchCase = False
             .Orientation = xlTopToBottom
             .SortMethod = xlPinYin
             .Apply
             
         End With
     
     Next Byt_j
    
    With .Range("$A$8:$T$305")
    
        .AutoFilter 2, "<>False"
        .AutoFilter 7, "<>False"
        
    End With
  End With
End Sub

It's the <ws.Sort.SortFields.Clear> function that appears to be the problem. Can anyone help me to code this so that it's compatible with 2003?

Many thanks,
AdLoki

Sorry I can't look at your code at the moment.

However this example code might give you a start

Sub Sort2003_7()

    If Application.Version <= 11# Then
        Columns("A:B").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
    Else
        With ActiveSheet.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, _
                            Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:B" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If

End Sub

You don't need to use the if statement, 2007 will handle the 2003 code.

Hope this is of some help.

Hi Marcol,

Thanks, this is the line I was already pursuing when you posted your response. Unfortunately, the following code is still kicking out errors

Sub VT_Filter()
Sheets("Dashboard").Calculate

Dim ws As Worksheet
Dim Str_Ce1, Str_Ce2, str_Key1, str_Key2 As String
Dim Byt_j As Byte        
Dim rge_Sort As Range

Set ws = Sheets("CHART DATA")
  ws.Visible = True
  ws.Select
  With ws
    .Range("$A$8:$T$305").AutoFilter
    
     For Byt_j = 1 To 4
                
        Str_Ce1 = Choose(Byt_j, "9", "52", "123", "208")
        Str_Ce2 = Choose(Byt_j, "50", "121", "206", "305")

        Set rge_Sort = ws.Range("A" & Str_Ce1 & ":T" & Str_Ce2)
        str_Key1 = "B" & Str_Ce1 & ":B" & Str_Ce2
        str_Key2 = "E" & Str_Ce1 & ":E" & Str_Ce2
        
        Sheets("test").Range("A1").Value = str_Key1
        
        'rge_Sort.Sort.Clear
        
            rge_Sort.Sort _
                Key1:=ws.Range("str_Key1"), Order1:=xlAscending, _
                Key2:=ws.Range("str_Key2"), Order2:=xlAscending, _
                header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
                Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 
            'SORT BY PCT THEN BY QUALIFIER
        
         'With rge_Sort.Sort
        
         '    .SetRange Range("A" & Str_Ce1 & ":T" & Str_Ce2)
         '    .header = xlGuess
         '    .MatchCase = False
         '    .Orientation = xlTopToBottom
         '    .SortMethod = xlPinYin
         '    .Apply
        
         'End With
     Next Byt_j
    
    With .Range("$A$8:$T$305")
    
        .AutoFilter 2, "<>False"
        .AutoFilter 7, "<>False"
        
    End With
  End With
  
  ws.Visible = xlSheetVeryHidden
  
End Sub

I'm now getting a 1004 error (Method 'Range' of object '_Worksheet' failed). Debugger highlights the underlined code above.

I also still haven't figured out how to replicate the <With rge_Sort.Sort> and <rge_Sort.Sort.Clear> functions. Having looked around, I gather it is something to do with 2007 sorting objects which is not back-compatible functionality but, because I'm a n00b, I don't really understand how to resolve this...

Any help is appreciated.

Cheers,
AdLoki