I am trying to sort columns based on their rank.
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.
- Sub Macro10()
- ' Ranks and Column Re-arranging on CT_Calc Sheet
- Dim CTRank As Integer
- MyRows = Cells(Rows.Count, 1).End(xlUp).Row
- MyColumns = Cells(1, Columns.Count).End(xlToLeft).Column
- Range("A1").Offset(708, 0).Select
- ActiveCell = "RankCT"
- Range(Cells(MyRows, 2), Cells(MyRows, MyColumns)).Name = "CTRange"
- For CTRank = 1 To MyColumns - 1
- Range("A1").Offset(MyRows, CTRank).FormulaR1C1 = "=RANK(R[-1]C, CTRange, 1)"
- Next CTRank
- Range(Cells(MyRows + 1, 2), Cells(MyRows + 1, MyColumns)).Name = "RankRange"
- MsgBox Range("RankRange").Find("4").Address
- End Sub