1. ## Random Numbers (2003)

Hi Loungers - My question is this. I'm using a =RANDBETWEEN(5,55). Is there away to stop the random number generation once it has generated them the first time and/or is there away to force the generation with a command after the first generation?

2. ## Re: Random Numbers (2003)

Functions involving RAND or RANDBETWEEN will generate a new random number whenever the workbook is recalculated, unless you set calculation to Manual in the Calculation tab of Tools | Options. If you do that, you can still force a recalculation (and hence new random numbers) by pressing F9.

If you want to freeze the numbers definitively, copy them to the clipboard and do a paste special > values. This will remove the formulas, so the values will not change any more.

3. ## Re: Random Numbers (2003)

If you do not mind using VBA code then the following user defined function may assist. Once it calculates a value it will not change unless you request a full recalculation by pressing Ctrl+Alt+F9

The function has an optional 3rd arguement which specifies how many decimal places you want the result to be. If missing the result is an integer

=RANDOMBETWEEN(5,55) - generates a random number between 5 & 55
=RANDOMBETWEEN(5,55,2) - generates a random number between 5 & 55 with 2 decimal places

<code>Public Function RandomBetween(Lowest As Long, Highest As Long, Optional Decimals As Integer)</code>
<code> If IsMissing(Decimals) Or Decimals = 0 Then</code>
<code> Randomize</code>
<code> RandomBetween = Int((Highest + 1 - Lowest) * Rnd + Lowest)</code>
<code> Else</code>
<code> Randomize</code>
<code> RandomBetween = Round((Highest - Lowest) * Rnd + Lowest, Decimals)</code>
<code> End If</code>
<code>End Function</code>
<code></code>

4. ## Re: Random Numbers (2003)

Great - Thanks Hans

5. ## Re: Random Numbers (2003)

Tony,

Thanks for the reply - will give it a shot - One question though, how do I specify which cells I want the random numbers generated in?

6. ## Re: Random Numbers (2003)

Put the function Tony posted in a standard module, and enter a formula like <code>=RANDOMBETWEEN(5,55)</code> in each cell that should contain a random number.

7. ## Re: Random Numbers (2003)

Thanks again Hans

8. ## Re: Random Numbers (2003)

Hans, will this work in Access as well? Having alittle trouble with getting the field in a form to have the number generation.

KST

9. ## Re: Random Numbers (2003)

Access doesn't have RAND or RANDBETWEEN.

You can use the VBA function Rnd instead of RAND, for example by entering the following formula in the Control Source property of a text box on a form:

=10*Rnd()

You can also use Rnd to define a calculated field in a query, but if you use something like this:

RandomValue: Int(10*Rnd()+5)

you will get the same value in all records. To return a different value in each record, provide a number field as argument to Rnd:

RandomValue: Int(10*Rnd([ID])+5)

where ID is the name of a number field.

10. ## Re: Random Numbers (2003)

Thank Hans.... I'm trying to do random numbers between 1 and 900. I'm using the query design you recommending as a 'Make-Table Query'. I'm getting duplicate numbers. I don't want duplicate numbers....
What cha think?

11. ## Re: Random Numbers (2003)

We're straying too far from Excel now. I have started a new thread in the Access forum with a possible solution: <post:=724,805>post 724,805</post:>. Please post replies there.

12. ## Re: Random Numbers (2003)

Try the formula way in Excel,

To do random numbers between 1 and 900, without duplicate numbers

1] A1, enter this formula :

=CEILING(RAND()*9,1)

2] A2, enter this array formula ( confirmed by Shift+Ctrl+Enter ) and copied down :

{=SMALL(IF(ISNA(MATCH(ROW(\$1:\$900),\$A1:A1,)),ROW(\$ 1:\$900)),CEILING(RAND()*(901-COLUMN()),1))}

Regards
Bosco

13. ## Re: Random Numbers (2003)

Hi Bosco,

I like your array formula, it's clever. But <!profile=KSTidmore>KSTidmore<!/profile> asked how to do this in Access.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•