Please Help. Rank Formula Macro

[anappleuser]

Hi Forum,

I am using this macro to apply Rank automatically. But I have to select the range manually and then I run the macro. Actually, I have data in more than 1500 Raws and selecting range manually and then run macro is very time-consuming. Is there any way to modify below macro to run for all 1500 raws? See attached image file also to get more idea. I want to apply RANK in between every "TOTAL".

Hope I am able to explain my question correctly.


Sub RankFOrmula()Selection.Offset(, 1).FormulaR1C1 = "=RANK(RC[-1]," & Selection.Address(1, 1, xlR1C1) & ")"
End Sub

[p45cal]

In your picture, in the second column, are those 1's the result of a formula, and all thee other numbers just plain numbers?
If so it might be quite easy to do this.

Better to upload an Excel file than a picture.

[anappleuser]

Hello Forum Member,

Thank you for showing interest in this thread.

Those "1"s are actually 100%, all the data is in percentage format but it just looks like this coz I pressed Ctrl+~ to display formulas. I have attached excel file for your reference.

Could you guide me on this?

[p45cal]

Quick and dirty solution. In your attached file, first select the cels A2:A100, then run this macro:

Sub blah()
  Set S = Selection.Columns(1).Cells(1)
  For Each cll In Selection.Columns(1).Cells
    If cll.Value = "Total" Then
      Range(S, cll.Offset(-1)).Offset(, 2).FormulaR1C1 = "=RANK(RC[-1]," & Range(S, cll.Offset(-1)).Offset(, 1).Address(1, 1, xlR1C1) & ")"
      Set S = cll.Offset(1)
    End If
  Next cll
End Sub

[anappleuser]

Hello Forum Member,

Thank you very much for your guidance and support. Your "Dirty" solution works like a charm and solved my problem.