Making a random number 'stick'

[Rob Fenix]

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.

 

[Smitty]

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

 

[Chitosunday]

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

 

[Jay Petrulis]

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

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.

 

[Rob Fenix]

ADVERTISEMENT

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!

 

[Chitosunday]

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

 

[Jay Petrulis]

---cut---
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.

---cut---

Oops, make that Eric Desart and his page is
http://www.acoustics-noise.com/

Well worth a look.