Excel 2007 : Random numbers and static numbers

[ugsquish]

Ok here is what I am looking to do. I am using
randbetween(1,A5) *lets say the output is 5*
now I change the number in b25 the random number above will change.

Is there a way that I can get a random number, then keep that number static? I want a random number but once generated I don't want it to change if that makes any sence.

[jasoncw]

You have a couple options, but the short answer is you cannot use the RAND or RANDBETWEEN functions as you are describing.

1. (non-VBA) After entering the formula and obtaining the random number, copy and paste special > values the new number.

2. (VBA) You can use a VBA user-defined function that changes the random number into a static number.

Please Login or Register  to view this content.

Then the formula you would use would be =RandNum(1,A5)

3. (VBA) You can use a worksheet change event macro that changes the random number into a static number. For example, assuming you enter the RANDBETWEEN formula in column A, you can use:

Please Login or Register  to view this content.

HTH,
Jason

[ugsquish]

thanks. I will try those

------------------
edit: The vba worked like a charm. thank you very much

[rkwas1]

I would like to do the same thing that is discussed here, but do not understand VBA or how to make a user-defined function. Can someone explain this process to me in easy to understand terms? Thanks.