Making a random number 'stick'

Rob Fenix

New Member
Joined
Jan 21, 2004
Messages
2
I am using =rand()*99 to produce a random percentage, however, I do not want the generated number to change with every keystroke. I'd like it to be stored, in either that cell or, if that's not possible, another cell.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I am using =rand()*99 to produce a random percentage, however, I do not want the generated number to change with every keystroke. I'd like it to be stored, in either that cell or, if that's not possible, another cell.
Welcome the the Board!

You're not being very clear as to what you're trying to do. That formula should only change if you activate/exit that cell. And shouldn't it be =RAND()*0.99?

Do you have a Worksheet_Change event as well?

Try to post some of what you're trying to accomplish, using Colo's HTML Maker - you'll find the link at the bottom of the page.

Smitty
 
Upvote 0
you can just copy the cells with the formula, then click edit, pastespecial, values . If you want another distination for your values, then before clicking edit, click the destination cells first.


However, if you want a VBA code like the cell is in a1:a8 then
Range("a1:a8").Value = Range("a1:a8").Value.

You if want another destination like b1:b8 then just use:
Range("b1:b8").Value = Range("a1:a8").Value
 
Upvote 0
Hi,

Smitty -- Rand() is a volatile function, so it will recalculate upon any change to the workbook, not including formatting changes like adjusting column width, etc..

Workarounds...

1. Turn calculation to Manual (not recommended in most cases)
2. After entering the formula, choose Edit>PasteSpecial>Values. You will lose the formula but retain the value.
3. Write a UDF such as the following

Code:
Function StaticRand(Optional Multiplier as Double) As Double
    StaticRand = Rnd * IIf(IsMissing(Multiplier), 1, Multiplier)
    End Function

Note that VBA's Rnd has a different period than does Excel's RAND() function, but the difference would only be noticed when doing seriously intensive stats work.

4. Search this site for Eric Dysart and visit his webpage. He has a downloadable file that has exactly what you want, but requires the add-in to be loaded.

5. I am sure I am missing some option. :confused:
 
Upvote 0
I'm thinking the 'Paste Special' may give me what I'm wanting, since I don't really need the function after it has resolved once. Thanks for the help guys!
 
Upvote 0
Another approach in VBA, select the cells and run this code:Sub rndinput()
For Each sel In Selection
sel.Value = Rnd * 99
Next sel
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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