Making RAND() non-volatile?

[Help!Thanks]

I am using RAND() to randomly select items from half a dozen lists. Each list has only 2 to 7 items in it, but I need to make thousands of selections.
The problem is that every time I load the spreadsheet, RAND() automatically runs, making new selections, but I only want RAND() to run on my command, with F9, so that the selections won't be always changing. I know I can do this by entering the formula, "=RAND()" and pressing F9 instead of ENTER. But to do this by hand for thousands of entries, there must be a better way? Thanks!

 

[Boller]

I think you probably need to use VBA.

Provide details of the lists, etc. and how you are using RAND() to make selections.

 

[Weaver]

Here's an idea.

Colour in all the cells you want to have random numbers in, then insert this code into a module. The example uses red (interior.colorindex =3) but you could change it.

Whenever you run it, the formula is dropped in to all the red cells, then they are converted to values so they don't change.

Sub randthing()
    Dim r As Range, c As Range, a As Range
    Application.ScreenUpdating = False
    For Each c In ActiveSheet.UsedRange
        If c.Interior.ColorIndex = 3 Then
            If r Is Nothing Then
                Set r = c
            Else
                Set r = Union(r, c)
            End If
        End If
    Next c
    For Each a In r.Areas
        With a
            .Formula = "=rand()"
            .Value = .Value
        End With
    Next a
End Sub

HTH

 

[Help!Thanks]

Weaver,

".value = .value" Never would have thought of that! Works great now.

Thanks!!

 

[Weaver]

Weaver,

".value = .value" Never would have thought of that! Works great now.

Thanks!!

That one blew my mind a bit when I first encountered it.

Another thing, I can't remember why I elected to go through the range area by area! I think you can probably do it all in one go.

 

[Boller]

Another thing, I can't remember why I elected to go through the range area by area! I think you can probably do it all in one go.

Not if you want to use : .Value = .Value (The range needs to be contiguous). I think (but maybe not).

 

[Weaver]

Not if you want to use : .Value = .Value (The range needs to be contiguous). I think (but maybe not).

You might be right - it's probably what I was thinking at the time. I'm certain what you're saying is true for copy/paste actions outside of vb.

Edit, you're right - looks like I'm smarter than I think I am.

 

[Boller]

Edit, you're right - looks like I'm smarter than I think I am.

Don't you mean that you're no dumber than you look?

 

[Weaver]

Don't you mean that you're no dumber than you look?

Ah, but you don't know how dumb I look...