• 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...

  • 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

Status
Not open for further replies.

dwrowe001

Member
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
 
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.
 

Attachments

  • Pick5.xlsm
    33.5 KB · Views: 36
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.

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.
 
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.
 
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.

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
 

Attachments

  • Pick5 example.xlsm
    46.4 KB · Views: 21
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.
 

Attachments

  • Pick5 example_Ken.xlsm
    53.8 KB · Views: 24
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.


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
 
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.
 
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.


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
 
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.
 

Attachments

  • Pick5 example_Ken2.xlsm
    56 KB · Views: 30
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.

Hi Ken,
Are you kidding!! get me close, ha.... this is perfect.. thank you... I will never go any other site for VBA help..
 
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
 
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
 
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
 
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:
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.


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
 
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.
 
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.

I'm using windows 10, Excel 2016. Not sure which one is #10 file
 
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.
 

Attachments

  • Pick5 example_Ken2.xlsm
    56 KB · Views: 24
Last edited:
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..

Outstanding... works like a charm... now how do I get this worksheet and all macros into my other Lottery project workbook??
 
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.
 
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.


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???
 
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:

View attachment 46284

in the Sub:
View attachment 46285

the Clear All button works though???


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
 
Status
Not open for further replies.
Back
Top