Results 1 to 5 of 5
Thread: Random Number (Excel 2002)

20070710, 20:48 #1
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
Random Number (Excel 2002)
Edited by HansV to present data in table format
Hi,
I'd like to include some code in my macro to create a random 5 digit number for each company number. For example, you can see below that for Company number 99, the random number created is 86159, and this random number is then repeated for each line that has Company number 99. A new random number is created for each new company number. Any ideas?
Thanks!
Lana
<table border=1><td>Random #</td><td>Amount </td><td>Narrative</td><td>Unit</td><td>Nature</td><td>Line #</td><td>Co</td><td align=right>86159</td><td align=right>100</td><td>Testing</td><td>99CCORP</td><td align=right>8000140</td><td align=right>1</td><td align=right>99</td><td align=right>86159</td><td align=right>200</td><td>Testing</td><td>99CCORP</td><td align=right>8000145</td><td align=right>2</td><td align=right>99</td><td align=right>86159</td><td align=right>300</td><td>Testing</td><td>99CCORP</td><td align=right>8000147</td><td align=right>3</td><td align=right>99</td><td align=right>86159</td><td align=right>400</td><td>Testing</td><td>99BS</td><td align=right>2300600</td><td align=right>4</td><td align=right>99</td><td align=right>86159</td><td align=right>1000</td><td>Testing</td><td>99BS</td><td align=right>2300045</td><td align=right>5</td><td align=right>99</td><td align=right>58912</td><td align=right>50</td><td>New</td><td>85CCORP</td><td align=right>8000140</td><td align=right>1</td><td align=right>85</td><td align=right>58912</td><td align=right>60</td><td>New</td><td>85CCORP</td><td align=right>8000145</td><td align=right>2</td><td align=right>85</td><td align=right>58912</td><td align=right>110</td><td>New</td><td>85BS</td><td align=right>2300045</td><td align=right>3</td><td align=right>85</td></table>

20070710, 20:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Random Number (Excel 2002)
Try this (assuming the table starts in cell A1):
Sub FillRandom()
Dim r As Long
Dim n As Long
Dim lngRand As Long
Randomize
n = Range("G65536").End(xlUp).Row
For r = 2 To n
If Not Range("G" & r) = Range("G" & (r  1)) Then
lngRand = 10000 + Int(90000 * Rnd)
End If
Range("A" & r) = lngRand
Next r
End Sub

20070710, 21:26 #3
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Random Number (Excel 2002)
This works awesome of course... it's over my head at the moment. Can you explain the "10000 + Int(90000 * Rnd)" section of the code in words please. "Int" means "integer", right? "Rnd" means "random"? I've never used this formula before.
Thanks Hans!
Lana

20070710, 21:36 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Random Number (Excel 2002)
The Rnd function returns a random number between 0 and 1. The Randomize instruction at the beginning of the macro "shuffles the deck", without that you'd get the same series of random numbers each time you start Excel.
You wanted a five digit random number, i.e. a whole number between 10,000 and 100,000 (the latter not included). This range starts at 10,000 and contains 90,000 numbers.
By multiplying Rnd with 90,000, we get a number between 0 and 90,000 (with decimals).
The Int function discards the decimals and keeps the whole number. So now we have a whole number between 0 and 90,000.
Adding 10,000 gives use the result we wanted: a whole number between 10,000 and 100,000, i.e. a five digit number.

20070710, 21:39 #5
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Random Number (Excel 2002)
This makes perfect sense now... thanks so much for the detailed explanation Hans. I really appreciate your help!
Lana