RAND() result changes when other cells are used in worksheet.

[sbickley]

I have created some columns with random numbers between x and y, no problem.
But whenever I enter data into unrelated cells, the RAND() cells re-calculate
a new random value. How do I turn this recalculation feature off?

 

[Mike H]

Hi,

You could set calculation to manual but that may not be very helpful for
other formula in your sheet what you could do is use a macro to generate
non-updating random numbers. If you post some details someone will help.

Mike

 

[Charles Williams]

If you want your Random numbers to be static rather than dynamic, one easy
way isto Copy & Paste Special Values the cells containing the random numbers
(Edit - Copy ... Edit - Paste Special - Values)

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[JLatham]

Here is a 'general' solution. It permits you to define a sheet which will
contain the starting and ending cell address for the list to be created, and
lets you set the lower and upper limits for the random numbers generated.

To put it into your workbook, open the workbook and press [Alt]+[F11] to
enter the Visual Basic Editor (VBE). In the VBE, choose Insert --> Module
and copy and paste the code below into it. Change the values for constants
controlSheetName
resultsSheetName
startCellAddressIn
endCellAddressIn
lowerLimitIn
upperLimitIn
as required to tailor it to your setup. To run it, use Tools --> Macro
-->Macros and choose CreateRandomList from the list of macros in the
workbook and click the [Run] button. Hope this helps. If it's too generic
and you want to tailor it specifically for your workbook/worksheet setup,
give us more detailed information (sheet name where the list is to appear,
address of the cells that are to receive the random numbers, and the lower
and upper limits for the random numbers).

Sub CreateRandomList()
'for versatility, the starting cell address,
'the ending cell address, and the
'lower and upper limits for the
'random numbers are placed into cells
'on any sheet in your workbook, they
'can be on the same sheet with the random
'list, and that's what is assumed in this
'code:
' The starting cell is A2
' the ending cell is B101
' the above creates 2 columns of random
' numbers from A2 down to A101 and from
' B2 down to B101. If you just wanted
' one column from A2 to A101, then
' change the entry in cell B1 of your
' control sheet.
'Control setup:
'cell on sheet contents of cell
' A1 = starting cell address, as A2
' B1 = ending cell address, B101
' C1 = lower limit for random numbers, 1
' D1 = upper limit for random numbers, 200
'
'you could use a different sheet for the
'control sheet, so that's permitted, simply
'change the sheet names as required.
Const controlSheetName = "Sheet1"
'for this example, we assume the control entries
'are on the same sheet where the results are to
'be placed
Const resultsSheetName = "Sheet1"
'tell where the control values are
Const startCellAddressIn = "A1"
Const endCellAddressIn = "B1"
Const lowerLimitIn = "C1"
Const upperLimitIn = "D1"
Dim startingCell As String
Dim endingCell As String
Dim lowerLimit As Integer ' Long for very large numbers
Dim upperLimit As Integer ' again, 'As Long' for very large #s
Dim listRange As Range
Dim anyListEntry As Range

'set reference to the range of cells that
'will receive the random numbers
startingCell = Worksheets(controlSheetName). _
Range(startCellAddressIn)
endingCell = Worksheets(controlSheetName). _
Range(endCellAddressIn)
lowerLimit = Worksheets(controlSheetName). _
Range(lowerLimitIn)
upperLimit = Worksheets(controlSheetName). _
Range(upperLimitIn)
Set listRange = Worksheets(resultsSheetName). _
Range(startingCell & ":" & endingCell)
Application.ScreenUpdating = False
For Each anyListEntry In listRange
anyListEntry = Int((upperLimit - lowerLimit + 1) * Rnd + lowerLimit)
Next
Set listRange = Nothing ' housekeeping

End Sub