Thread: XL03 Sort error: "Method 'Range' of object '_Worksheet' failed"

Hi All,

I have had to modify some code written in XL07 to be back-compatible with XL03 (thread here) but the resulting code (below) keeps falling over with a 1004 error (Method 'Range' of object '_Worksheet' failed) at the line underlined:

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")
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 _
                Key1:=ws.Range("str_Key1"), Order1:=xlAscending, _
                Key2:=ws.Range("str_Key2"), Order2:=xlAscending, _
                header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
                Orientation:=xlTopToBottom, DataOption1:=xlSortNormal         

     Next Byt_j
    
     With .Range("$A$8:$T$305")
    
        .AutoFilter 2, "<>False"
        .AutoFilter 7, "<>False"
        
     End With
  End With
  
End Sub

Could anyone help with this?

Many thanks,
AdLoki

Change the sort keys to:

       str_Key1 = "B" & Str_Ce1
        str_Key2 = "E" & Str_Ce1

and then use:

       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