Hi Rick,

Tnx, Per Jessen said the same, and testing proved it.

Wkr,

JP



"Rick Rothstein" wrote in message
...
If you look at the Intelli-help popup as you type the command in the code
window, it shows this for the syntax...

Rank(Arg1 As Double, Arg2 As Range, [Arg3] As Double)

I know the help for this function says the second argument can be an array
or a range, but it appears that VB imposes a "range only" requirement on
this second argument.

--
Rick (MVP - Excel)


"JP Ronse" wrote in message
...
Hi Dana,

Tnx for your reply. The statement itself generates already the error
(compile error) and hitting <F5 highlights 'arr'?



Wkr,

JP


"Dana DeLouis" wrote in message
...
For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Hi. In general, you are looking for the number 1 within this array to
find its rank. The number '1 is most likely not there.

Having said that, I too am getting errors. I've tried all variations
that I can think of with no luck. Hopefully, someone will jump in.

= = = = =
Dana DeLouis
(using Excel 2007)

JP Ronse wrote:
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub