Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND() result changes when other cells are used in worksheet.
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND() result changes when other cells are used in worksheet.
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 "sbickley" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND() result changes when other cells are used in worksheet.
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 "sbickley" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND() result changes when other cells are used in worksheet.
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 "sbickley" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Same =rand() value in two adjacent cells in column | Excel Worksheet Functions | |||
how can i set up rand or rand between to give only -1 or 1? | Excel Worksheet Functions | |||
How do I copy a check box result from Worksheet A to Worksheet B? | Excel Worksheet Functions | |||
I cannot see the result of a SUM in the worksheet, why? | Excel Discussion (Misc queries) | |||
Updating 1 worksheet with result from another worksheet | Excel Discussion (Misc queries) |