Solved: Sort - convert code to work on 2003 ?

[bdsii]

I run Excel 2007 and have code that works perfectly with that version but just found out it will not work with Excel 2003. See sample spreadsheet attached.

I was aware to be cautious about the conflict and tried to eliminate as much as I could by using a 2003 version spreadsheet (.xls file) and I have tried to make sure the number of rows called out in the code do not go above the max for 2003. I thought the code I was using was compatible with both versions.

Upon trying to run the macro, I got a Run Time Error '438' - object doesn't support this property or method.

I commented out the code below and the macro ran fine. Now I need to convert the following sort to one that will work for Excel 2003.

I was not able to debug at the location that was using 2003 but I did quickly see the row highlighted for debugging was the second row of the code with clear at the end of the line. I cannot work from the site using 2003 and do not have access to another location that has 2003 to tinker with the code.

Can someone familiar with 2003 eyeball this and let me know what may be causing the error? I have tried searching but cannot find anything on this.

A second question: with VBA, does 2003 allow more than 3 sort columns (used below) or is it restricted to 3 columns?

Any advice ?


Sub sort()
Dim totalrows As Long
totalrows = 0
totalrows = ActiveSheet.UsedRange.Rows.Count

Rows("2:" & totalrows).Select
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Clear
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"C3:C" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"A3:A" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"I3:I" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"H3:H" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("HoursAvail").sort.SortFields.Add Key:=Range( _
"D3:D" & totalrows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

With ActiveWorkbook.Worksheets("HoursAvail").sort
.SetRange Range("A2:I" & totalrows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select

End Sub




Thanks in advance for the help !

[mbarron]

I used a two stage sort. Sorting in 2003 is limited to 3 columns at a time.

Try this one:
Sub sort2()
Dim totalrows As Long, sortRange As Range

totalrows = 0
totalrows = ActiveSheet.UsedRange.Rows.Count

Set sortRange = Worksheets("HoursAvail").Range(Cells(2, 1), Cells(totalrows, 10))

sortRange.sort key1:=Range("d2"), order1:=xlAscending, key2:=Range("h2"), order2:=xlAscending, _
key3:=Range("i1"), order2:=xlAscending, header:=xlYes, dataoption1:=xlSortNormal, _
dataoption2:=xlSortNormal, dataoption3:=xlSortNormal

sortRange.sort key1:=Range("c2"), order1:=xlAscending, key2:=Range("a2"), order2:=xlAscending, _
header:=xlYes, dataoption1:=xlSortNormal, dataoption2:=xlSortNormal
End Sub

[bdsii]

thanks for the help mbarron. I am marking this one solved.

However, I was not able to get the two stage sort to work correctly. For some reason, the sort did not place the rows into the order I needed. I commented out the second sort and changed the range fields in the first one to the 3 fields I really had to have. It worked as expected after that. I lost 2 sort fields but I can live with it as long as I have the first three.

I appreciate your help ! Yeeessshhhh.....this compatibility thing is awful as you experts know. I am a newbie and this is the first experience I have had fighting to make it work with a different version. It makes it tough when I cannot even test it. :-)

Thanks !