just learning
Hi Joel,
Thank you very much for spending your time to help me out of this.
The issue is that the statement
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)
does not allow to run the code.
See also the reply of Per.
Wkr,
JP
"Joel" wrote in message
...
Array start at zero when using worksheet functions in excel. See if these
changes help
Sub test_JP()
Dim arr(0 To 999) As Double
Dim i, v As Range
For i = 0 To 9
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
"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
|