# ranking a range in vba

#### dgs2001

##### Board Regular
Hi

Once again I am needing help, my understanding is ever growing but never complete

Code:
``````Function getSel(r2 As Range) As String

Dim  v2 As Variant,  r3 As Range, r4 As Range
Dim  rank1 As range, rank2 As range, rnk as integer

Set v2 = r2.Columns(21)

For Each r3 In v2
rnk = WorksheetFunction.Rank(r3, v2, 0)
If rnk = 1 Then Set rank1 = r3
If rnk = 2 Then Set rank2 = r3
Next

''' other stuff see below '''

End Function``````

I am passing a 75 column range to a function and need to workout which 2 rows in this range contain the first and second ranked numbers in column 21 of the range, this is what i hoped the above code would do, however it gives me a run time error 13 Type Mismatch.

I then need to work out which row in this range contains the highest ranked number in column 22

Assuming one row of my passed range satisfies both these criteria then I need to check whether this row is one of the 3 highest ranked in column 20

If one row of data passes all these criteria I will return the text contents of the first cell in that row as the Function output.

Any help is appreciated Thanks

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi I think I have solved this

I forgot the most basic of checks.
My data had some text in it which of course cant be ranked so I now have the following :-

Code:
``````Set v2 = Application.Index(r2, , 21)
For Each r3 In v2
If IsNumeric(r3) Then
rnk = WorksheetFunction.Rank(r3, v2, 0)
If rnk = 1 Then Set rank1 = r3
If rnk = 2 Then Set rank2 = r3
End If
Next``````

Of course if there is a better way anybody wishes to suggest then please do

Thanks

Replies
9
Views
336
Replies
1
Views
188
Replies
0
Views
299
Replies
2
Views
128
Replies
7
Views
211

1,196,112
Messages
6,013,538
Members
441,770
Latest member
Griggsy28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back