Want to create a RANK FUNCTION in VBA

[oleppedersen]

Hi, sorry if this is answered somewhere else; if so I was unable to track it down.

I am pretty good with Excel, but have little knowledge of VBA.

I have a massive database where everything is ranked by various criteria.

I would like to create a RANK function in VBA instead, so that the 1.5 million RANK commands in Excel can be dispensed of, hopefully speeding up the process.

I have tried a bit myself, also with some help of "Learn VBA"-sites, to no avail. When I use the Worksheet.Function.Rank syntax listed over at Microsoft, my Excel 2013 (Norwegian version) won't do what I would like.

As soon as I crack the basics, I think I am savvy enough (thanks to learning BASIC in the early 1980-ies) to create a loop.

Lets say my data that needs to be ranked are in range A1:A10, and I need to put the ranking (descending) in C1:C10. What should the code look like?

Here is my abysmal try:

Function Rangering() As Integer
Dim Liste As Range
Set Liste = Range("A1:A10")


WorksheetFunction.Rank(Range("A1"),Liste)


End Function


---
Unfortunately, noone around that I can ask for help where I work

 

[eddvrs]

This should help you:

Sub rangering()

    Dim liste As Range
    Set liste = Range("A1:A10")
    
    For Each cell_a In liste
        Range(cell_a.Address).Offset(0, 1).Value = WorksheetFunction.Rank(cell_a.Value, liste, 1) ' change 1 to 0 for desceending rank
    Next cell_a

End Sub

This will add the numbers rank one cell to the right of the range of numbers. So, if your range is A1:A10, B1:B10 shows the ranks...

I hope this helps!

 

[oleppedersen]

This will add the numbers rank one cell to the right of the range of numbers. So, if your range is A1:A10, B1:B10 shows the ranks...

I hope this helps!

Brilliant! Thank you so much - not only does it work, I also understand better than before how the FOR/NEXT have to be used. I owe you one