# 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?

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

End Sub

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

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