sorting problem with range definition

[Janis]

Here is my macro, I think the range is still wrong?
I put the recorded macro sort which works here:
-----recorded macro-----

Sub Sort()

' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 27).End(xlUp))

rng.Sort key1:=Cells(17, 2), Order1:=xlAscending, _
key2:=Cells(19, 2), Order2:=xlDescending, _
key3:=Cells(1, 2), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

since it is bad form to use a selection in a macro I'm trying to write it
the right way but I can't get the range right. This one doeesn't sort:

--------

Sub Sort()
'
' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 27).End(xlUp))

rng.Sort key1:=Cells(17, 2), Order1:=xlAscending, _
key2:=Cells(19, 2), Order2:=xlDescending, _
key3:=Cells(1, 2), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

End Sub

thanks, again

 

[Jim Thomlinson]

If I am correct you are missing some dots. Also Cells() is first row then
column so you probably need to reverse the keys. Try this...

Sub Sort()

' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 27).End(xlUp))

rng.Sort key1:=.Cells(2, 17), Order1:=xlAscending, _
key2:=.Cells(2, 19), Order2:=xlDescending, _
key3:=.Cells(2, 1), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

 

[Janis]

Jim, thanks, I missed the "." dots on the cells as in .cells.
Thanks,