1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with VBA Code for generating lotto numbers

Discussion in 'VBA Macros' started by dwrowe001, Oct 6, 2017.

  1. dwrowe001

    dwrowe001 Member

    Messages:
    32
    Hi everyone,

    I would like help with coming up with a vba code which will help me generate 5 two digit number groups.. yes, this is lottery related... lol.....

    anyway, say I have a number list, which could vary in length, starting in A1 and could go down as many as 40 numbers.

    the numbers in the list could be any number between 01 to 60, never higher then 60. So it could look like this:

    12
    10
    09
    03
    11
    20
    02
    05
    19
    14
    06
    45
    on down....

    From this list I would like to generate sets of 5 2 digit groups, like this:
    02 03 05 06 09
    05 11 14 19 45
    02 05 11 12 19
    10 11 14 19 20
    the lottery I'm tracking is 5/60... 5 numbers picked out of number range 01 - 60

    I would like a box or drop down option where I could select the how many number groups are generated, 5, 10, 15, 20. So if I select 5, it will generate only 5 random groups of numbers taken from the list in column A.

    The groups of numbers generated could start C1 and go to G1, and go down however many rows needed.

    Also, if not to much, like in the video below, have 5 text boxes where if left empty, all 5 numbers would be picked from list. if I put in a number 5 say in the #2 text box, then all numbers generated would have the number 5 in the second position. similarly if I put a 55 in the last text box then all generated numbers would have for the last number 55.
    but if I leave all text boxes empty then all 5 numbers would be picked for me... hope I haven't asked for to much.. but I just seem to figure out how to do it...

    I found this link on youtube it is close to what I'm looking for. I've tried modifying it and all my pitiful attempts have failed miserably.... I can't figure out what to do.

    youtube.com/watch?v=zMi6B984ynI

    Thank you in advance to any help.

    Dave
  2. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    As your preferred numbers go up, the longer it will take to find a true random set. Speed could be improved but would take more code. If no entries or all entries filled, then no preference is set so speed is fast.

    I have seen some forum rules against this sort of thing though not here. There should be lots of similar examples out there. Most probably don't allow the preferred numbers though.

    Attached Files:

    AlanSidman and NARAYANK991 like this.
  3. dwrowe001

    dwrowe001 Member

    Messages:
    32
    Ken,
    Thank you for your time and effort doing this for me.. It is almost what I need.. I just wish instead of choosing from a list of all numbers 1 to 60 the code could be able to choose numbers from a list provided by me.

    Based on the code you provided, I have much to learn... it almost seems like it's to much. Thanks again.
  4. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    I added comments to help you understand parts. Press F1 with cursor in or next to a command word to get extended help.

    Don't let custom Functions or Subs keep you from using them. In the mMain Module the routines, I posted my function to return a non-repeating random integer array and array sort routines. Don't even shy away from seemingly long routines. The more features one adds and more chances for error, it takes more code if you want to help others and yourself.

    As for choosing from a list of all numbers, as you can see in my function, you can add those by a variable. Attach an example file if you need to show what you want. I don't know how you want to pick those.

    You can pick numbers by one of several means. e.g. Listbox(), Combobox(), TextBox(), InputBox(), Application.InputBox(), etc. A discontinuous integer pick: Range A2 to A6 has: 5 55 -1 20 3 values. One wants to pick one or more of those. My routine does that. There are 5 values. So, the inputs are 1,5,1. The returned array say a, would have one element base 0. So, if a(0)=3, then 3+1 means that the 4th cell in the range A2 to A6 randomly picked was 20. As you can see, my routine provides a lot of flexibility.

    As shown in that video, =RandBetween() does some of what my routine does. Though I say my routine, and it is, we all learn from each other and build on that if we learn enough.
    NARAYANK991 likes this.
  5. dwrowe001

    dwrowe001 Member

    Messages:
    32
    Great, I'll try the F1 for help.... I really do appreciate your help with this Ken..

    I am attaching a file as an example of what I'm looking for.. I hope it explains better what I would like.

    Basically what I have is an Excel spreadsheet where I'm tracking numbers.. I then have formulas (I'm getting better with regular formulas) which narrow down choice numbers to choose from based on past draws, etc... I then list those numbers (Column A in example). That list is random, and can be as few as 10 number or as many as 40. I would like to be able to choose numbers from that list only. If possible, I would like the ability to choose how many picks to generate..

    Also, in the example, under R1, R2, R3, R4 and R5 there are empty boxes.
    If those boxes are empty or Blank, then the code would generate or pick all 5 numbers.. if I put a number in one or more of those boxes, say in Box 1 and 3 as in the example, then those two numbers would remain in all the picks generated, and the code would choose the remaining 3 from the list. Does make sense???

    The Clear All button would clear all picks and how many box.

    if you do your magic and create a code that can do that I'd be totally grateful.
    Thank you so much for your help.

    Dave

    Attached Files:

  6. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    I changed 2 letters for the first cell so the userform Sub Generate puts the data in the right location.

    I changed the Generate code to use the Form and ActiveX controls on your sheet. So, that Form CommandButton Generate now does what the userform does. I also tweaked a few sheet control names and formats.

    All that is left is what you really wanted. I noticed that you used 1 as the value for textbox1. 1 is not a value in column A values. As such, I will have to account for that in the code. The main thing that I will add is to remove duplicates in case column A values are duplicated or a duplicate occurs due to the added column A selection feature.

    In any case, here is the latest file in case it interests you.

    Attached Files:

  7. dwrowe001

    dwrowe001 Member

    Messages:
    32

    Wow, I am totally impressed at how quickly you can put something like this together.. I read somewhere on one of many sites I've visited in my attempts to learn VB, that it's easy to learn... LOL.... I DON'T THINK SO!!

    Anyway, what you have done so far is almost perfect... you said that I used a 01 in textbox1... yes, I would like to, if I choose, is to be able to insert any number (even if it's not in column A) into any of the 5 textboxes so that, that number I put in will be used instead of a number being picked... And If I leave those boxes empty or Blank, then the code would generate or pick all 5 numbers.. so, if I put a number in one or more of those boxes, say in Box 1 and 3 as in the example, then those two numbers would remain in all the picks generated, and the code would choose the remaining 3 from the list. like what I had in the example file I sent.

    Another thing I noticed in the last file you sent, the numbers generated aren't being taken from the number list in column A. those are the only numbers that should be used when picks are generated. Instead it looks like random numbers are still being chosen up to 60.

    I hate to be nit picky, cause I know you are going out of your way to help me with this code... I will understand if you get tired of my constant requests..
    You have done way more then I've been able to do on my own, or for that matter way more then what anyone else on other sites have done for me... and for this I am grateful!!!!

    Dave
  8. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    It is not a problem. That is what the forum is for.

    Right, 1-60 is in it now but is easily changed to suit your needs. I will work on it and post back when done.

    For the longer projects, I may stop every so often and help someone else for a short problem. Normally, I fully solve a problem before working on another. At least as best I understand what the problem is.

    For your problem, I went ahead and made 2 other files that generate numbers for 2 other lotteries with a 6th number with a different range. Once you learn common concepts like this, helping others with similar problems is easy. Eventually, one will run across a solution method that is faster and more powerful. These days, most problems have some general concept in common. Still, there are many ways to solve problems so I learn something along the way sometimes as well.
    NARAYANK991 likes this.
  9. dwrowe001

    dwrowe001 Member

    Messages:
    32

    True, when I first started my lottery number Excel project I knew very little about regular formulas... just the very basic stuff, but the more I did, the more I learned, and the more I learned I would then expand my project and be forced to learn more.. I'm in the neophyte stages of VB now.. but I am learning. Some of the stuff you're doing has me floored. I would love to be able to sit down and pump out something when needed... Guess I'll get there eventually. Hats off to you sir.
    Dave
  10. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    This should get you close.

    When you see +1 and -1 in the code, that is to account for Base One or Base 0 array elements. Array "a" should have been set 0 to 4 to make it a bit more clear. Still, one has to deal with the +/- 1 issues at some point since controls tend to be suffix name Base One. e.g. TextBox1, TextBox2, etc.

    Attached Files:

    dwrowe001 likes this.
  11. dwrowe001

    dwrowe001 Member

    Messages:
    32
    Hi Ken,
    Are you kidding!! get me close, ha.... this is perfect.. thank you... I will never go any other site for VBA help..
  12. dwrowe001

    dwrowe001 Member

    Messages:
    32
    I'm serious.. no other site helped me at all with this code.. I'm not sure what the problem was. Not sure if no body knew how to do it, or if they just flat out didn't want to take the time to help? But I am eternally grateful to you for what you have done for me so far. Thank you

    Dave
  13. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    Thanks, flowers are always nice.

    Many can help depending on the forum. I visit about 9 per day, 8 are Excel VBA. There are several that do the same. Ergo, the cross-posting etiquette you may notice sometimes. Some don't want to help with the bigger projects or in this case, may think it is against their religion. I like to help for topics that have no responses or interest me or are challenging as I sometimes learn something myself along the way...

    cheers
  14. dwrowe001

    dwrowe001 Member

    Messages:
    32
    It was just very frustrating.. I can't help expressing my appreciation for your help.

    Are you still trying to work out the one remaining issue? Where, if I choose to insert any number (even if it's not in column A) into any of the 5 textboxes, that number that I put in will be used instead of a number being automatically picked... If I leave those boxes empty or Blank, then the code would generate or pick all 5 numbers.. so, if I put a number in one or more of those boxes, say if I put 03 in TextBox 1 and 20 in TextBox 3, then those two numbers would remain in all the picks generated, and the code would choose the remaining 3 for TextBox2, 4 and 5 from the list. the result would be for example:
    03 10 20 33 45
    03 12 20 31 55
    03 05 20 40 44
    03 19 20 25 30
    03 04 20 29 51

    the above example assumes that I have selected How Many sets at 5. also, the numbers I choose (If I choose to do this) don't necessarily have to be numbers in the number list in column A.

    Dave
  15. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    It was working that way for me in my tests. Is it not so for you? I'll go test again

    Just put the number without the leading 0. It should not matter though. The 5 just means generate 5 sets. The default would be 1. Of course as one adds more default numbers in the textboxes, the time for each set increases.

    While I like your idea of a set number in one location like 21 in textbox2, I am thinking that a nice feature would be to set preference numbers but not be set on location occurrence. e.g 21 in textbox1. Then 21 could occur in any location like textbox5. e.g. 1 2 3 4 21.

    I just tested both generates, they both keep the set default numbers. The userform generate still does the 1-60 method. I could change it but you wanted the sheet textbox method with some set to pick from. and the location preference.
    Last edited: Oct 8, 2017
  16. dwrowe001

    dwrowe001 Member

    Messages:
    32

    No it's not working for me... i type in a number into any of the textboxes and i get a run time error.. i click on debug and it takes me in the code:
    .sort 'sort ascending
  17. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    Are your running it on Windows or a Mac? The sort routine assumes Windows with the .Net framework files installed.

    Are you using the #10 file?

    For such a short sort, many other sort routines like bubble sort are out there.
  18. dwrowe001

    dwrowe001 Member

    Messages:
    32
    I'm using windows 10, Excel 2016. Not sure which one is #10 file
  19. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    Thread posts are numbered in lower right below the reply box when posted. #10 is the ken2 file.

    I tried to make it fail typing the letter "0" and 1. Val() converts that to 1 so that can't be your issue. There is an error check for number so that would be interpreted as zero in one part of the code.

    Try a reboot. It should work as any xp+ system should have the .Net framework files that it uses. I have a feeling something else is going on. A reboot sometimes solves Windows issues. I just hope the automated cars don't run Windows...

    I have seen sorts fail when letter and numbers are sorted. I made an advanced sort routine for that but it should never be needed for this.

    I added ken2 here again just for giggles.

    Attached Files:

    Last edited: Oct 8, 2017
  20. dwrowe001

    dwrowe001 Member

    Messages:
    32
    Ok I figured out what you mean by File #10... I re downloaded it again and saved it... Its working... I'm a little slow sometimes..
  21. dwrowe001

    dwrowe001 Member

    Messages:
    32
    Outstanding... works like a charm... now how do I get this worksheet and all macros into my other Lottery project workbook??
  22. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    Drag and drop is one easy way. Sheets should be obvious. With two workbooks open, you can drag and drop Modules, Userforms/Forms, and Classes. If object name exists in target, rename in source, drag and drop, then rename in source. Renaming is done in the View menu's Property window for objects selected in Project Explorer.

    Of course you can export and import the VBE Modules, Userforms/Forms, and Classes. This is why you may want to choose descriptive names for your objects. UserForm1 would not be a good choice.

    You can do the same for Sheets but they export as a Class which is just the VBA code for that sheet object. VBE View menu has Project Explorer where right clicking to import/export and drag and drop is easy.

    Of course things like Workbooks(1) or Sheet1 will need changed in the code. VBE's Debug menu's Compile will show you where those need changed if one is missed.
  23. dwrowe001

    dwrowe001 Member

    Messages:
    32

    Well, should have know I'd mess this up.. I copied the modules and worksheet over to my other workbook. process looked like I moved everything ok, but get this error when I try to generate some numbers in my other workbook:

    upload_2017-10-9_14-1-9.png

    in the Sub:
    upload_2017-10-9_14-2-0.png

    the Clear All button works though???
  24. Kenneth Hobson

    Kenneth Hobson Member

    Messages:
    79
    You have the routine in more than one public location. It is called a name collision.
  25. dwrowe001

    dwrowe001 Member

    Messages:
    32

    Ok, I got that fixed, I had an extra Module with the Vba code in it.. I deleted the code and it works now, except now, the generated picks start in Cell E2 vice E5 were they used to start..

    upload_2017-10-9_14-29-44.png

Share This Page