Modify ranking code

[roberto1111]

Good day friends,

I'm using this code to select the 10 lowest values and give them a ranking from 1 to 10.

My query;

Is it possible to modify this code so that it will give the lowest values the opposite ranking from which it is now. (Please take a look at the example sheet).

Thank you for any help offered,


Robert

Sub TST()
    
    Dim A As Integer, B As Integer
    For A = 1 To 15
        For B = 1 To 10
            If Range("B1").Cells(A, 1) = Application.WorksheetFunction.Small(Range("B1:B15"), B) Then
                With Range("B1")
                    .Offset(A - 1, 1) = B
                End With
                Exit For
            End If
        Next B
    Next A
    
End Sub

[shg]

Change Small to Large?

[roberto1111]

I mean that the code should still select 10 of the lowest values but give the opposite ranking,

on the example sheet the code now gives the following ranking;


500 7
100 1 << This should be 10
1000
250 3
900
750 10 << This should be 1
1300
400 6
200 2
900
300 4
700 9
350 5
600 8
1100

and so on..

[johnjohns]

Try the rank function with 'order' as 0 or 1 and see the difference
ie if your data range is E4:E13, try these

=RANK($E4,$E$4:$E$13,1)

=RANK($E4,$E$4:$E$13,0)

rgds

johnjohns

[roberto1111]

Hello John,

I got an error message while trying the formula,

How should I set formula exactly?


Thanks!

[johnjohns]

See the attachment. Also do not tag your thread as 'solved' until you get the desired solution. You may not get the help from others then

rgds

johnjohns