Making RAND() non-volatile?

Help!Thanks

New Member
Joined
Jun 20, 2011
Messages
2
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I think you probably need to use VBA.

Provide details of the lists, etc. and how you are using RAND() to make selections.
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top