Results 1 to 8 of 8

Sort code won't sort as desired

Threaded View

rhorgan Sort code won't sort as... 11-25-2019, 08:18 AM
romperstomper Re: Sort code won't sort as... 11-25-2019, 08:26 AM
rhorgan Re: Sort code won't sort as... 11-25-2019, 08:29 AM
romperstomper Re: Sort code won't sort as... 11-25-2019, 08:32 AM
rhorgan Re: Sort code won't sort as... 11-25-2019, 08:45 AM
romperstomper Re: Sort code won't sort as... 11-25-2019, 09:40 AM
rhorgan Re: Sort code won't sort as... 11-25-2019, 10:08 AM
nigelog Re: Sort code won't sort as... 11-25-2019, 09:30 AM
  1. #1
    Registered User
    Join Date
    11-22-2019
    Location
    Dublin, Ireland
    MS-Off Ver
    MS Office 365 Pro Plus
    Posts
    7

    Sort code won't sort as desired

    Hi

    I have created a macro to populate the Premiership table with a variety of statistics. Part of the code is supposed to sort the teams based on points, goal difference if points are equal, and goals for if points and goal difference are equal.
    However, it won't seem to sort based on goal difference unless I remove the part of the code that sorts based on goals for. The sort code is as follows:

    For Each cell In team
    cell.Activate
    
    'Sort by points
    If ActiveCell.Offset(0, 8) <> ActiveCell.Offset(1, 8) Then
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("J1"), Order:=xlDescending
        .SetRange Range("B1:CA21")
        .Header = xlYes
        .Apply
    End With
    
    'Sort by goal difference if equal on points
    ElseIf (ActiveCell.Offset(0, 8) = ActiveCell.Offset(1, 8)) And (ActiveCell.Offset(0, 7) <> ActiveCell.Offset(1, 7)) Then
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("I1"), Order:=xlDescending
        .SetRange Range("B1:CA21")
        .Header = xlYes
        .Apply
    End With
    Range("B2").Activate
    
    'Sort by goals for if equal on points and goal difference
    ElseIf (ActiveCell.Offset(0, 8) = ActiveCell.Offset(1, 8)) And (ActiveCell.Offset(0, 7) = ActiveCell.Offset(1, 7)) Then
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("G1"), Order:=xlDescending
        .SetRange Range("B1:CA21")
        .Header = xlYes
        .Apply
    End With
    Range("B2").Activate
    
    End If
    Next cell
    The problem is shown in this picture:
    Error1.PNG

    As you can see, the order should be Sheffield United, Burnley and then Arsenal because that is the descending order of goal difference. Instead it is sorted in descending order of goals for.
    Note also that Tottenham and Man United should also be swapped.

    How can I fix this?

    Thanks
    Last edited by rhorgan; 11-25-2019 at 10:08 AM. Reason: Problem solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 08-17-2017, 02:22 AM
  2. [SOLVED] vba code to sort data in two sheets and copy and paste sort data in output sheet
    By UPA in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-03-2017, 05:13 AM
  3. Help to make VBA sort, summarize and paste data into desired order/grouping
    By JBoldt in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-22-2016, 03:33 PM
  4. [SOLVED] Trying to sort results before pasting (where to insert "sort code")
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-09-2014, 10:24 PM
  5. [SOLVED] How to sort the data by item code first, then sort them by date?
    By Zecond in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-04-2013, 01:55 AM
  6. Adding a sorting button to a table to sort as desired
    By mj_bowen in forum Excel General
    Replies: 1
    Last Post: 06-10-2012, 11:37 AM
  7. Sort Files into desired directories
    By tek9step in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 02-17-2010, 05:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1