Hi,

I perform simulations for items 1 to 30 nsim times. Result of simulation 1 is stored in sheet named "sim" and row 1 column 1 to 30.

Now I have function that takes a subset of items and checks how many times each item in subset will have rank = 1, I am only interested to compare items in subset.

Sub testMyFunction()

Dim arr() As Variant
Dim result() As Variant

arr = Array(3, 4, 6, 8)

region = MyFunction(arr, nsim)

Function MyFunction(arr() As Variant, nsim) As Variant

Dim rng As Range, h As Range
Dim holder() As Variant

'Setting relevant range
Set rng = Sheets("sim").Cells(1, arr(0))
For i = 1 To UBound(arr)
    Set h = Sheets("sim").Cells(1, arr(i))
    Set rng = Union(rng, h)
Next i

'holder
ReDim holder(0 To UBound(arr))

For i = 1 To nsim
    For j = 0 To UBound(arr)
        If Application.WorksheetFunction.rank(rng(i, arr(j)), rng.Offset(i - 1, 0), 0) = 1 Then
            holder(j) = holder(j) + 1 / nsim
        End If
    Next j
Next i
            
MyFunction = holder
Function throws me error 1004 "Unable to ghet the rank property o fthe worksheetfunction class" unless arr(0) = 1. I guess rng that I create in beginning of function doesnt make any sense for VBA..

Can anyone see how I can adjust formula so I can calculate rank for each item in arr where the range is = to rng?