View Full Version : [SOLVED] Please Help. Rank Formula Macro

02-27-2018, 10:42 AM
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

02-27-2018, 02:08 PM
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.

02-28-2018, 07:50 AM
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?

02-28-2018, 08:26 AM
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

02-28-2018, 10:19 AM
Hello Forum Member,

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