Help with Ranking and Rearranging Columns

  • I am trying to sort columns based on their rank.


    Code
    1. Range("RankRange").Find("4").Address

    However, I look for the column number each rank - I keep getting an error. I am new at VBA. When I copy and paste the values in the cells and then do the find it works. Not sure what to do. Finally, I want to sort the columns based on the rank in ascending order. If someone can help with this, it would be much appreciated. Thank you.


  • Sorry, but the workbook you upload is not workable. So many named ranges that it runs out of memory even if I try to copy something. Also, I don't see what it is you are trying to accomplish - I see columns of data but which are you attempting to rank? on which worksheet?


    A simpler example would be more useful - one worksheet with some data and totals of some kind to rank, and one worksheet with the expected results of the ranking.

    If I've been helpful, let me know. If I haven't, let me know that too.

  • I have attached the simplified sheet. I want to achieve the following


    1. Rank the Sum Totals of each column (ROW 708), for that the VBA code is inserting the Rank formula in the row below the totals.

    2. Following in the rank, I want to rearrange each of the columns so the the smallest rank column is first, followed by the second, until the nth column

    3. After that I want to sort the contents of each column in Descending order, column 1 first, then column 2, until nth column.


    I hope this is clear. Thank you.

  • I tried another code, that sort of works in finding the rank and reordering the columns.


    It goes haywire if the column is in its correct place (Column 1 is already in column 1, then it can't insert a column before it and paste) and also for the last column, as it is also in the correct place. How do i correct this?



  • This worked on the file in post #5

    If I've been helpful, let me know. If I haven't, let me know that too.