Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting problem with negative value (excel 2000)

    Hello guys ...long time no see..:-))

    I need your guys help once more..

    I got a sheet that count marks for my student

    the problem is when i sort the student grades..it will

    separate them to 2 part..i mean theres a gap between the list..

    i think this ocuured becaise i give negative value to student who get D

    and E grades..

    i want the names to be continous like when i sort them by their names..thanks

    please refer at the attachment

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting problem with negative value (excel 2000)

    The problem is that sort column can contain 4 types of values. They can contain numbers: people can have scores >0, scores =0, and scores >0. All of these will sort as desired. The problem is the "blank names" will all have a score of 0 so the blanks will always come before negative scores (not what you want).

    The easisest fix is to not include the blank names in the sort, but to only include the scores with names. In your macros you select the entire range:
    <pre>Range("b6:ah48").Select</pre>


    If you, instead, only select the range with names:
    <pre>Range(Range("AH6"), Range("B65536").End(xlUp)).Select</pre>


    You will not have the problem.

    This range selects AH6 as one corner, and the corner from the last occupied cell in col B.

    Steve

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting problem with negative value (excel 2000)

    Hi NiuB,

    You are correct that the reason is the negative values.

    The 'blank' cells are sorted as zeroes, so you need to exclude them from the sort. You can do this by changing the Range you Select for the Sort.

    Instead of ..

    <font color=6495ed>Range("b6:ah48").Select</font color=6495ed>

    .. try using something like ..

    <font color=blue>Range("b6", Columns("ah:ah").Find("*", , xlValues, , , xlPrevious)).Select</font color=blue>

    Enjoy,
    Tony

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting problem with negative value (excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 18-Apr-04 06:43. Thought of another failure to add)</P>One comment on this method.

    It works because the spreadsheet has the options to not display any zero values at all (tools - options - View - "zero values") which "effectively" turns all zeroes in the spreadsheet as "non-values" and does not display them.

    If the zeroes had been "hidden" in another way (eg with formatting color) it would not work, since the formulas will otherwise yield a value (0) that is just not displayed.

    Steve
    PS the method will also fail if the person if the last name in the list has a zero score, since the zero will not be listed and will not be included in the sort since the so Mr or Ms "Z.." will be last in the Grade sort as well as the name sort since he/she will not be included in the sort.

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting problem with negative value (excel 2000)

    Right on both counts, Steve <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting problem with negative value (excel 200

    Thanks to you guys both..sdckapr and TonyJollans ..

    So far so good..but I've still not tested it with multiple condition yet..

    If any problem occured again..I will post again..cheers

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting problem with negative value (excel 200

    Welcome back NiuB,

    Try changing your code to the following :

    Sub SusunGredBerlian()

    Dim strPwd As String
    Dim intCustList As Integer

    strPwd = ""

    With Application
    .ScreenUpdating = False
    .AddCustomList ListArray:=Array("1", "-1", "0")
    intCustList = .CustomListCount
    End With

    With Sheets("berlian")
    .Unprotect Password:=strPwd
    .Range("b6:ah48").Sort _
    Key1:=Range("ah6"), _
    Order1:=xlDescending, _
    Header:=xlYes, _
    OrderCustom:=intCustList + 1, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom
    .Protect Password:=strPwd
    End With

    With Application
    .DeleteCustomList ListNum:=intCustList
    .ScreenUpdating = True
    End With

    End Sub

    Andrew C

  8. #8
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting problem with negative value (excel 200

    Hiii back andrew..its been quite sometime since i come here..

    thanks andrew..the code works like charm..:-))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •