ranking a range in vba

[dgs2001]

Hi

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

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

Please Help, I'm getting more confused by the minute!

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

 

[dgs2001]

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 :-

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