# Thread: Random Number (97 SR2)

1. ## Random Number (97 SR2)

I would like to randomly generate an 8 digit number in VBA, any ideas?

Thanks!

2. ## Re: Random Number (97 SR2)

=INT(RAND()*100000000)

3. ## Re: Random Number (97 SR2)

Looks great!

Now, how might I enforce 8 digits at all times? For example, if this formula returns a six digit number, is there a way to place the preceding two zeros before the number?

if I can't do it in a cell, can it be done in a userform's text box?

Thanks again!

4. ## Re: Random Number (97 SR2)

format the cell as:
00000000

Great!

Thanks Bob!

6. ## Re: Random Number (97 SR2)

Just a note, using this method causes the cell to regenerate a random number any time formatting changes in the sheet.

Does anyone know of a method I can use in VBA which I may then assign to the cell?

Thanks!

7. ## Re: Random Number (97 SR2)

use range("x").formula = range("x").value -- but what's the problem if it recalcs?

8. ## Re: Random Number (97 SR2)

I need the number to remain static after it's generated, as it will be used for future reference...

9. ## Re: Random Number (97 SR2)

ok, then after it's been calculated and you want to keep it, copy/paste special values in place.

10. ## Re: Random Number (97 SR2)

Drk,

You could aslo create a user defined function as such functions do not by default update on a sheet recalculate.<pre> Function udfRAND(lRnd As Long)
Application.Volatile (False)
udfRAND = Int((lRnd * Rnd))
End Function.</pre>

The above function will provide a random number between 1 and the value used as an argument, e.g <pre> udfRAND(100000000)</pre>

should provide a number between 1 and 99999999.

If you change the Application.Volatile argument to True, the function will update on a sheet recalculate.

Andrew C

#### Posting Permissions

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