# Lottery in VBA

#### zip.survey

##### Board Regular
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Scott Huish

##### MrExcel MVP
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.

#### zip.survey

##### Board Regular
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.

#### zip.survey

##### Board Regular

ADVERTISEMENT

anybody? is this even possible to match?

#### A1058

##### New Member
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)

#### A1058

##### New Member

ADVERTISEMENT

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?

#### shippey121

##### Well-known Member
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

#### A1058

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

#### shippey121

##### Well-known Member
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

Replies
0
Views
318
Replies
5
Views
252
Replies
5
Views
202
Replies
1
Views
651
Replies
1
Views
91

Threads
1,137,193
Messages
5,680,086
Members
419,880
Latest member
suarezprado

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

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