Using a function to return a non-volatile random selection

[Tulyderrin]

Hi All, - 1st post.
I've lurked around here for a while - some really useful hints & tips !


I have a range of postcodes in a column. (The sheet is FARMDATA the Column is G:G) There are 1000's of cells each with a unique code.

I've written an amateurish vba function to generate a single random selection of those postcodes :

[/vba]

Function RandCell(Rg As Range) As Range

Set RandCell = Application.WorksheetFunction.index(Rg, Application.WorksheetFunction.RandBetween(1, Application.WorksheetFunction.CountA(Rg)), 1)

End Function

[/vba]

When I use this function in a cell =RandCell(FARMDATA!G:G) it successfully selects a random postcode from the list in FARMDATA column D and returns it to the cell.

But - everytime I change another cell on that sheet, my random selection also changes.
I know this is because "RandBetween" is Volatile.

Is there some code that I can add to my RandCell function that gets around this ?

I've been trying to use xlpastespecial.values - I read somewhere that just pasting values makes them static but I cannot figure that out..........

like I say - amateur !

thanks a lot everyone ...... any pointers gratefully accepted.

Tulyderrin

 

[Kenneth Hobson]

Function RandCell(Rg As Range)
  RandCell = Evaluate("Index(" & _
    Rg.Address & ", RandBetween(" & _
    "1, CountA(" & Rg.Address & ")), 1)")
End Function

 

[Tulyderrin]

Hi Kenneth - thanks for the reply - it pointed me in the right direction.

As written it returned 0 - but I substituted the actual range and it worked fine.

Is there any easy mod to make sure that the selected cell isnt repeated ever ?

thanks for your help.

 

[Tulyderrin]

Hi Kenneth,

there are 2 enhancements I'm trying to figure out now.........

I'd like to also get the next 2 consecutive cells after the random pick - and paste their values to 2 new cells - so I have a random selection of 3 consecutive entries from the FARMDATA!G2:G26 range - and for those to be static.

I also have to avoid duplicates in the final selections - so if one of the three values is repeated, then I need a new random cluster of 3. (The list is entirely unique alphanumeric codes with no repeats)

thanks so much for your help.

Tulyderrin