working with ranges in custom function

[dgs2001]

Hi All
I posted the following on Mr Excel.com

http://www.mrexcel.com/forum/excel-q...lications.html

It is currently unanswered and I think as its a vba question, this would have been the better forum to post it in.

If anybody can help please post a response there.

Thanks Dgs2001

[dgs2001]

Ok I think I solved this.

I forgot the basics again !

my column of data had some text so I needed a basic Isnumeric check.


Although solved I am open to suggestions to a better or more efficient code.

Thanks

[SamT]

So you expect me to sign up for another forum to see your code?

Any way, Rank ignores Text.

You've declared v2 as a Variant, then forced VBA to guess what you want when you assign a range to it.

For Each r3 In r2.Columns(21)
    rnk = WorksheetFunction.Rank(r3, r2.Columns(21), 0)

Have you considered what will happen if you have two cells ranked #1?

Rank1 will be set to the second cell ranked #1 and there will be no cell ranked #2.

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