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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
I think you probably need to use VBA.

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

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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:

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,152
Messages
5,485,058
Members
407,479
Latest member
jbone2020

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top