Rank on non-contiguous ranges

[samot79]

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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?

[xlnitwit]

Hi,

I think you might need to create the range for each iteration of i

Please Login or Register  to view this content.

[samot79]

Thanks!

This seems to work.

But isnt it strange that my solution works as long first item in arr is = 1?

[xlnitwit]

I would suspect it is only checking the first two columns in that situation.