Lottery in VBA

zip.survey

Board Regular
Joined
Sep 8, 2006
Messages
55
I've searched all over for a simple code and can't find it. All I want to do is randomly display six different numbers that can be anywhere from 1-52 but cannot be duplicated in those six boxes.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Why do you need VBA for this?

In A1, put =RAND()
Copy down to A52.
In C1, put =RANK(A1,$A$1:$A$52)
Copy down to C6
C1:C6 will hold your 6 random numbers.

Hit F9 to recalculate new sets.
 
Upvote 0
this is great. but I need to be able to run multiple scenarios of this at once. so it doesn't just so 6 random numbers at once. but like 30 or so scenarios of these 6 numbers.
 
Upvote 0
i think this will do what you want. not sure if its the best way but i tested and it works.....it doesnt copy down correctly so youll have to manually edit g6 -g10 for g6 just change the A5:A10's to B5:B10 and g7 is C5:C10 and so on to the end

g5= =COUNTIF(A5:A10,A1)+COUNTIF(A5:A10,B1)+COUNTIF(A5:A10,C1)+COUNTIF(A5:A10,D1)+COUNTIF(A5:A10,E1)+COUNTIF(A5:A10,F1)
 
Upvote 0
hello.. i was thinking about this... and for curiousitys sake id sure like to have a loop run just for fun to see how many times the numbers need to refresh before you get all 6 of your picks to match on any one of the 6 tickets. can anyone help with this?
 
Upvote 0
are you running some sort of lottery and trying to get a lottery checker, this can be done with array formulas

i do the same thing at work, i have everybodys number stored in a spreadsheet and then just enter the weekly numbers that come out, all numbers that have been drawn highlight using conditional formatting, if you can wait til tommorow i can email you a blank copy of my file, the file i use also incorporates more than one weeks numbers if you need it to cant quite rememer the formula i used
 
Upvote 0
shippey that sounds good. id like to have a look at your sheet. but still im looking for help with a loop. just to satisfy my curiousity on how many times the code would need to refresh before you get all 6 numbers to match. or basically about how many tickets youd need to buy :)
not that me or most ppl would or even could buy that many. still i am just curious. the loop is probably an easy thing ofr one of the guru guys here ...
 
Upvote 0
Book1
ABCDEFG
1number1number2number3number4number5number6
2staff11844948211
3staff231162081011
4staff32132139143
5staff435124333373
6staff5433678418
7staff6102829252633
8staff7492041424833
9
10
11
12draw11844
13draw2311620810
14draw3
15draw4
16draw5
Sheet1



i managed to work out the array formula, it is

Code:
=OR(B2=$B$12:$G$16)

set you sheet out as in my example then in your first row of numbers my example B2 goto conditional formatting and select fomula is and enter the above formula. choose formats as required

then click ok, right click on the first number and click copy, highlight the rest of your numbers to be checked and right click, Paste Special >> FORMATS this will copy the formats to the rest of your cells,

now enter numbers in the draws boxes and number drawn will highlight

the B2 in my formula is the first cell of my numbers to be checked
the =$B$12:$G$16 is the range of the draw1 to draw5 cells adjust as required but remember to use absolute refrences ($) in this part

HTH
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top