+ Reply to Thread
Results 1 to 4 of 4

Rank on non-contiguous ranges

  1. #1
    Forum Contributor
    Join Date
    01-28-2013
    Location
    sweden
    MS-Off Ver
    Excel 2010
    Posts
    108

    Rank on non-contiguous ranges

    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?

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Rank on non-contiguous ranges

    Hi,

    I think you might need to create the range for each iteration of i
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Contributor
    Join Date
    01-28-2013
    Location
    sweden
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Rank on non-contiguous ranges

    Thanks!

    This seems to work.

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

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Rank on non-contiguous ranges

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 02-20-2014, 07:03 PM
  2. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  3. [SOLVED] Help with Non Contiguous Row Ranges
    By Chriz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2013, 01:04 PM
  4. Contiguous "Range" from Non Contiguous Ranges
    By DonkeyOte in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-03-2011, 03:23 PM
  5. Copy and Paste an array (contiguous & non contiguous ranges)
    By Xrull in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2010, 09:17 AM
  6. Sorting Non Contiguous Ranges
    By tekman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-24-2009, 04:58 PM
  7. [SOLVED] Adding Non-Contiguous Ranges
    By COE in forum Excel General
    Replies: 11
    Last Post: 01-24-2006, 07:50 PM
  8. sorting non contiguous ranges
    By gsh20 in forum Excel General
    Replies: 1
    Last Post: 09-08-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1