Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    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>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  3. #3
    3 Star Lounger
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  5. #5
    3 Star Lounger
    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

Posting Permissions

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