+ Reply to Thread
Results 1 to 6 of 6

VBA Macro to generate groups of 3 random numbers without repetition

  1. #1
    Registered User
    Join Date
    09-15-2016
    Location
    Malaysia
    MS-Off Ver
    Windows 7
    Posts
    5

    VBA Macro to generate groups of 3 random numbers without repetition

    The following VBA Macro generating repeated numbers.

    Please Login or Register  to view this content.
    Option 1 (which is preferred). Cells C1, D1 and E1 should show randomized Integer number between 0 and 9. Cells C1, D1 and D1 should NOT show same(repeated) numbers. This should continue all the way to Cells C1440, D1440 and E1440 where Cells C1440, D1440 and E1440 should NOT show same (repeated) numbers.

    Alternatively, Option 2. Cell C1 should be a randomized number between 0 and 7. Cell D1 should be should show a randomized number between (greater than the value in Cell C1) and 8. Cell E1 should show a randomized number between (greater than the value in the Cell D1) and 9. This way it will not show any repeated numbers in any of the cells. This continue all the way to Cells C1440, D1440 and E1440 where Cells C1440,D1440 and E1440 should not repeated(same) numbers.

    Your help will be greatly appreciated.

    Thanks.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,315

    Re: VBA Macro generating repeated numbers

    Note change below, though I have not tested it. Changes in red. This implements option 1.

    There is no need to call Randomize each time you call Rnd. You only need to do it once. It can actually disrupt the random distribution of your numbers if you do this.

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,097

    Re: VBA Macro to generate groups of 3 random numbers without repetition

    Please Login or Register  to view this content.
    Last edited by jindon; 10-15-2016 at 01:08 AM.

  4. #4
    Registered User
    Join Date
    09-15-2016
    Location
    Malaysia
    MS-Off Ver
    Windows 7
    Posts
    5

    Re: VBA Macro generating repeated numbers

    Hi Jeff. Thanks for your help.

    My VBA programme was written for the Option 2. I tried your version and guess what it worked fine.

    Please Login or Register  to view this content.
    So I made some changes as

    Please Login or Register  to view this content.
    Now I want to modify the programme even further. Now instead of getting a random number between 0 and 9, I would like to get in between 1 and 9.

    How should I change the programme accordingly?

    Thanks and Regards.

  5. #5
    Registered User
    Join Date
    09-15-2016
    Location
    Malaysia
    MS-Off Ver
    Windows 7
    Posts
    5

    Re: VBA Macro to generate groups of 3 random numbers without repetition

    Hi Jindon,

    I truly appreciate your help.

    Thanks and Regards

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,315

    Re: VBA Macro to generate groups of 3 random numbers without repetition

    You want a range of 1-9, so that is a scale of 8. Applying a scale of 8 gets you to 0-8, then you need to add 1.

    Int(Rnd * 8) + 1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Generate Random Numbers Between 0-9
    By dreicer_Jarr in forum Excel General
    Replies: 11
    Last Post: 01-17-2014, 01:00 AM
  2. [SOLVED] Generate Random numbers where sum = 1
    By sajeel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2013, 07:12 AM
  3. generate random numbers
    By fo05kka in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2009, 09:55 PM
  4. Using VBA to generate random numbers
    By matt3542 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-02-2008, 09:15 AM
  5. Random numbers without repetition
    By Mike7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2008, 07:44 AM
  6. Generate Random Numbers
    By Rgaherty in forum Excel General
    Replies: 5
    Last Post: 10-30-2007, 04:08 PM
  7. [SOLVED] How do I generate non-recurring random numbers to assign groups?
    By hkoehnk in forum Excel General
    Replies: 1
    Last Post: 09-12-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1