randomly select value WITHOUT changing

[Doyle Brunson]

I am using a formula to randomly select one of the values in a list.

However, the value returned by the formula changes with each
recalculation (F9), and I do not want that to happen.


IS THERE A WAY TO GENERATE A RANDOM VALUE FROM A LIST WITHOUT IT
CHANGING WITH EACH RECALCULATION?

The reason I ask is that I have combo boxes in the worksheet which
makes it recalculate whenever they are used.

PART 2: Is it possible to assign the formula to a button so that the
update only occours when it is pressed?

***
FYI formula used to formula to randomly select one of the values in a
list:
INDEX, ROUND, RAND, and COUNTA functions in the following formula:
=INDEX(A2:A7,ROUND(RAND()*COUNTA(A2:A7),0))

 

[sirknightly]

Doyle,

To my knowledge, you can't control when an Excel formula updates. You
can, however, create a macro that does this. Paste the code below into
VBA (ALT+F11) and it will randomly choose a number from A2:A7 and
deposit the value into the currently active cell. You can link this
code to a button if you like. Be sure that the sheet with the datalist
is currently selected (this shouldn't be a problem if you put your
button on the same page).

Here's the code:

'---------------------
Sub RandomListSelect()

ListRange = ActiveSheet.Range("A2:A7")

CountAProxy = Application.WorksheetFunction.CountA(ListRange)

ActiveCell.Value = Application.WorksheetFunction.Index(ListRange,
Round(Rnd() * CountAProxy, 0))

End Sub
'---------------------

Knightly

 

[Doyle Brunson]

It's working perfectly. Thank you for a useful macro.

 

[tx12345]

Hi

I tried the macro and it kept stopping. It keeps saying "Variable not
defined" while highlighting "ListRange =" in the macro.

I have

4
7
2
14
12
16

showing in A2:A7 on a blank worksheet. Not sure where the uniqe random
number is supposed to be.