+ Reply to Thread
Results 1 to 16 of 16

VBA random numbers NO repeats

  1. #1
    Registered User
    Join Date
    08-17-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    27

    VBA random numbers NO repeats

    Hi. I have created a quiz to a foreign language. And in the background, I have a form control button to pick numbers at random. But once a random number has been picked, I don't want it to repeat that number ever again. How would I go about doing that? See the attached spreadsheet. Thanks.Hebrew Verb Generator - Mac.xlsm
    Last edited by jamiegfinch; 12-16-2015 at 12:39 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    Re: VBA random numbers NO repeats

    Create a record of all possible numbers on a worksheet, and flag those that have been picked by your code. An easy way it to list all your numbers in column A, then put "used" in column B after they have been chosen. Then sort A and B based on column B to move all the unused values into a block, and then use RANDBETWEEN with the low and high row numbers of the block to give you an index to base your random choice on the block of numbers that have not been flagged as used.

    And if you ever need to reuse your numbers, delete the flag and start over.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    08-17-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: VBA random numbers NO repeats

    Hi Bernie. Thanks heaps for your reply. I don't think that I'm quite understanding you. Are you able to modify the spreadsheet and re post it?

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,340

    Re: VBA random numbers NO repeats

    I don't want it to repeat that number ever again.
    What's going to happen when all the numbers (possibilities) have been used which currently is only 81?

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,340

    Re: VBA random numbers NO repeats

    Try this where each number will be shaded green once used so it can't be used again until the shading is removed:

    Please Login or Register  to view this content.
    The user will be altered if every number has been used.

    HTH

    Robert

  6. #6
    Registered User
    Join Date
    08-17-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: VBA random numbers NO repeats

    This is only in testing stage, there will be a whole stack more words. But if they have all been used up, then I would prefer it to reset back to the beginning.

  7. #7
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,340

    Re: VBA random numbers NO repeats

    But if they have all been used up, then I would prefer it to reset back to the beginning.
    So you can use my code, just un-highlight the cells shaded green in Col. A.

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    Re: VBA random numbers NO repeats

    Randomly trying a set number of time is bad, even if you try a lot of times.

    As an example, trying to select randomly from 100 numbers 100 times when only 1 is available means that you have about an equal chance of not finding it (actually 36.6% chance of not finding it - 0.99^100 : you have a 99% chance of not matching it each time, for 100 trials) Even with 3 available numbers, you would still have a 5% chance of not matching (0.97^100)

    Try this to show what happens, with 100 numbers

    Please Login or Register  to view this content.


    You would be better off looping to check for an available value until you find one, rather than only trying lRow number of times. Of course, that would mean knowing that a value is available, so you would need to check first.

    So, staying with your code base and technique:

    Please Login or Register  to view this content.
    Of course, a better method would be to remove the value from a copy of the data set when it is used, and then just returning a random member of the shortened list

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 12-16-2015 at 10:52 AM.

  9. #9
    Registered User
    Join Date
    08-17-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: VBA random numbers NO repeats

    Hey Trebor76. That code you gave works really well. But for some reason, when I keep clicking the button it eventually brings up an error and when I check which cell it has highlighted, it is cell 82 (of which there is no number). Where am I going wrong in the code, that makes the button bring that cell up?

  10. #10
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,340

    Re: VBA random numbers NO repeats

    Have you tried Bernie's nifty solution? The last one be posted looks good to me

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,482

    Re: VBA random numbers NO repeats

    Different method...
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,340

    Re: VBA random numbers NO repeats

    Hi jamiegfinch,

    Here's another (more efficient as it doesn't loop) attempt by me:

    Please Login or Register  to view this content.
    Regards,

    Robert

  13. #13
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: VBA random numbers NO repeats

    I had to smile when I read this post.

    MANY years ago (30) I wrote a card game and had the same probblem. Once a card was picked from the deck it could not appear in the game again. And so, like the amateur and uniformed programmer that I was at the time, I would simply loop, over and over, looking for cards that hadn't been played.

    As you might suspect, the program ran quickly early in the game, but LATE in the game it was very slow... as it searched and searched for cards that hadn't been played.

    On the final move of the game, it took 60 seconds or so to find that last card. (Computers were very slow back then, compared to what they are today.)

    Bernie mentions this post #2 and post #8.

    Another, similar method is the following: Determine all of the random numbers available, store them in an array, and then simply shuffle this array! (Make one pass through the array and swap that element with a random element.)

    That's it. Now, simply select the first element in the list, and then the second element, and then the third element, and then the fourth, etc.

    You can now proceed down the list sequentially because the list is already in random order, after your shuffle.

  14. #14
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    Re: VBA random numbers NO repeats

    I like that analogy - shuffle the deck once, and it is randomized for the rest of the deal. I wrote a card game, a long time ago, and just shuffled once before all the cards were dealt out at the start. I think I used a 2D array, and bubble sorted on the randomly filled dimension.
    Last edited by Bernie Deitrick; 12-17-2015 at 08:55 PM.

  15. #15
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,340

    Re: VBA random numbers NO repeats

    Another, similar method is the following: Determine all of the random numbers available, store them in an array, and then simply shuffle this array! (Make one pass through the array and swap that element with a random element.)
    That's what my second does!!!

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,482

    Re: VBA random numbers NO repeats

    Quote Originally Posted by Trebor76 View Post
    That's what my second does!!!
    Those guys don't seem to understand what your/my codes are doing.

+ 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. [SOLVED] Generating Random Numbers between Upper & Lower Limits and without Repeats.
    By e4excel in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-17-2012, 09:42 AM
  2. Record Repeats of random function
    By gutkinma in forum Excel General
    Replies: 4
    Last Post: 09-09-2011, 02:19 AM
  3. Excel 2007 : Random seating chart no repeats
    By jhw69 in forum Excel General
    Replies: 1
    Last Post: 09-04-2011, 02:06 PM
  4. Excel 2007 : Random number generator without repeats
    By HRJames in forum Excel General
    Replies: 4
    Last Post: 03-24-2011, 11:37 AM
  5. Formulae for: 4 most repeats,4 least repeats in a series of numbers
    By Sedge in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2010, 04:56 AM
  6. Replies: 3
    Last Post: 03-21-2010, 09:04 PM
  7. Generate random numbers with no repeats
    By Paul Sheppard in forum Excel General
    Replies: 4
    Last Post: 04-06-2007, 12:37 PM
  8. code not working properly - VBA beginner (random numbers generation, no repeats)
    By msburza in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-11-2006, 01:10 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