CODEWITHSUNDEEP
vbaexcel
skroutela
skroutela

Reputation: 125

VBA Code for uniform Ranking in excel

I wrote this vba code for ranking

Sub RankArray()
    With Range("Distribution").Offset(0, 1)
        .FormulaArray = "=RANK(Distribution,Distribution)"
    End With
End Sub

but it is giving me a result like :

 1 2 3 4 4 6 7 7 7 10 11 12 12 14

Not in sequence because some rank are getting repeat

I want the only VBA code not excel formula for ranking that will give me result like below:

1 2 3 4 4 5 6 7 7 7 8 9 10 10 11 12

Upvotes: 0

Views: 857

Answers (1)

Rory
Rory

Reputation: 34055

You would need a different formula for that kind of ranking. Something like:

Sub RankArray()
    With Range("Distribution").Offset(0, 1)
        .Resize(1).FormulaArray = "=SUM(--ISNUMBER(MATCH(ROW(INDIRECT(""1:""&RANK(R[0]C[-1],Distribution))),RANK(Distribution,Distribution),0)))"
        .FillDown
    End With
End Sub

Upvotes: 1

Related Questions