Cells in Ranges Help

[Hacktastic]

I have a loop that calls the following proceedure

Public Sub sortert(rx As Integer, cx As Integer)
'
' Macro2 Macro
'
Dim r As Range
Dim sortrange As Range
extr = rx + 17
extc = cx + 7
sc = cx + 5
With sortrange = Range(Cells(rx, sc), Cells(extr, sc))
End With
With r = Range(Cells(rx, cx), Cells(extr, extc))
End With

    r.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
        sortrange), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange r
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

keeps on shooting back invalid block if, im trying to dynamically set my 2 ranges by giving them 2 different cell locations. any thoughts?

Chris

 

[SkipVought]

hi,

Try this...

Public Sub sortert(rx As Long, cx As Integer)
'
' Macro2 Macro
'
    Dim r As Range
    Dim sortrange As Range
    Dim extr As Long, extc As Integer, sc As Integer
    
    extr = rx + 17
    extc = cx + 7
    sc = cx + 5
    
    With ActiveSheet
    
        Set sortrange = Range(.Cells(rx, sc), .Cells(extr, sc))
        
        Set r = Range(.Cells(rx, cx), .Cells(extr, extc))
    
        With .Sort
            .SortFields.Clear
        
            .SortFields.Add _
                Key:=sortrange, _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal
            
            .SetRange r
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Skip,

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