Save RAND select number

[Guest]

Hello,

I want to use RAND BETWEEN to pick a random number from a list - and then
save it so it will not recalcutate. My intention is to use it as a way to
randomly select a winner of a drawing.

I tried to password protect the work sheet, but that didn't work. And, I'm
not sure why. Is there a way to randomly select a number, row or cell and
NOT have it recalculate?

 

[Guest]

You could copy and paste value.

 

[Guest]

I actually tried that on a second workskeet, but that didn't work either.
And, for legal purposes, I'd have to be able to show that a formula was used
to pick the number...so you can see how recalculating presents a problem...
Thank you for your response though.

 

[Guest]

Here is one possible solution... It is a UDF that takes 2 cells (the between
values) as inputs and returns a random number. Change either of the two cells
and the formula re-calcs...

Public Function StatRandBetween(ByVal Cell1 As Range, _
ByVal Cell2 As Range) As Long
StatRandBetween = Round(Abs(Cell1.Value - Cell2.Value) _
* Rnd + Application.Min(Cell1, Cell2))
End Function

Place this in a Standard Code module (the same place that recorded macors
reside). It is used like this...

=StatRandBetween(A1, B1)

Change A1 or B1 and it re-calcs... Otherwise it's static...