MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Minor Sort Problem


Posted by Glenn on April 23, 2001 8:17 AM

I have a minor problem with the macro below:

Key1 sorts percentages and Key2 sorts teams.

In the second grouping Key2:=Range( _"A11")refers to teams 6, 7 , 8, 9 and 10.

When all teams are at .ooo percent and the group is sorted then team 10 jumps to the top of its group and I have a list of team 10, 6, 7, 8, 9. How can I adjust the sort so that team 10 remains at the bottom of the list when the percentages are all .000?

ActiveSheet.Unprotect Password:="secret"

Range("A4:D8").Sort Key1:=Range("D4"), Order1:=xlDescending, Key2:=Range("A4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

Range("A11:D15").Sort Key1:=Range("D11"), Order1:=xlDescending, Key2:=Range( _
"A11"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A18:D21").Sort Key1:=Range("D18"), Order1:=xlDescending, Key2:=Range( _
"A18"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A24:D28").Sort Key1:=Range("D24"), Order1:=xlDescending, Key2:=Range( _
"A24"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A31:D36").Sort Key1:=Range("D31"), Order1:=xlDescending, Key2:=Range( _
"A31"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A39:D43").Sort Key1:=Range("D39"), Order1:=xlDescending, Key2:=Range( _
"A39"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

ActiveSheet.Protect Password:="secret"

End Sub



Posted by IML on April 23, 2001 3:06 PM

Don't know the answer but...

the problem is it is sorting as if it were text. If it weren't VBA, I'd say to a quick Data-Text to columns on it. Sorry if I'm stating the obvious.


Posted by Dave Hawley on April 24, 2001 12:01 AM

Re: Don't know the answer but...


Hi Glenn

If you have .000% in your cells are you sure they are numbers and not Text. The easiest way to tell is if they are right aligned or not. All numbers are right aligned by default.

The other problem may well be that, because you have Header:=xlGuess it may be assuming that your first row of data it is trying to sort is a header. Change it to either xlYes or XlNo


DaveOzGrid Business Applications

Posted by Big Bob on April 24, 2001 8:23 AM

:::::::::::::If you change 6 to 06,7 to 07 etc then it should sort correctly then after the sort you need to remove the leading zeros Swapped one problem for another Ho hum