Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random Number (97 SR2)

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

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Number (97 SR2)

    =INT(RAND()*100000000)

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Number (97 SR2)

    format the cell as:
    00000000

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Number (97 SR2)

    Great!

    Thanks Bob!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Number (97 SR2)

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

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Number (97 SR2)

    I need the number to remain static after it's generated, as it will be used for future reference...
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •