Yet another Lottery number macro needed

alexb

New Member
Joined
Mar 7, 2002
Messages
17
I need your help.
I have to generate random numbers (about 500 of them) that are 8 digits long but both the 5th and the 8th need to be the same. ie digit five will always be 8 and digit eight will always be a 1 while all the other numbers will change.

I am happy either have 500 rows or simply have a "click button" that will randomly generate the numbers each time in the same 8 columns.
This message was edited by alexb on 2002-05-02 07:05
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
G'day,

any other special rules we should know? like are all 500 numbers expected to be different? If not, you could use something a bit more simple like:

=(Randbetween(0,9)&Randbetween(0,9)&Randbetween(0,9)&Randbetween(0,9)&8&Randbetween(0,9)&Randbetween(0,9)&1)+0

with a cell formatting of custom "0000000#"

Hope that helps,
Adam
 
Upvote 0
Adam,

Still need more help - I can't just type the formula in a cell and if i paste it into VBA then it won't know what cell to put the numbers in.

Sorry to be so lame.
 
Upvote 0
tis is east yo edit as you require, i credit my great pal Dave Hawley http://www.ozgrid.com for this one, i cant find my lotto numbers i designed. which was quite cool and very simple.

Sub RandomNumberGenerator()
'Creates a list of random numbers _
between 1 and 36 in range A1:F6
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim Rw As Integer, Col As Integer
'Clear the range ready for random numbers
Range("A1:B18").Clear
Randomize ' Initialize random-number generator.
For Col = 1 To 2 'Set the Column numbers
For Rw = 1 To 18 'Set the Row numbers
'
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Do Until WorksheetFunction.CountIf _
(Range("A1:B18"), Cells(Rw, Col)) = 1
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Loop

Next Rw
Next Col
End Sub
 
Upvote 0
Try the following vba below. The goal is to create 500 unique numbers following your constraints:

Code:
Option Explicit
Sub Ran()
Dim upr As Integer, lwr As Integer
Dim upr2 As Integer, lwr2 As Integer, cell As Range
Dim LastRow As Long, myrng As Range, c As Range
Dim SearchValue As String
upr = 9999 'upper1 integer limit
lwr = 1000 'lower1 integer limit
upr2 = 10 'upper1 integer limit
lwr2 = 99 'lower1 integer limit
Application.ScreenUpdating = False
For Each cell In [a1:a500]
Randomize
cell.Value = Int((upr - lwr + 1) * Rnd + lwr) & "8" _
& Int((upr2 - lwr2 + 1) * Rnd + lwr2) & "1"
test: 'For uniqueness that is
LastRow = cell.Row - 1
If LastRow = 0 Then GoTo 1
Set myrng = Range("a1:a" & LastRow)
Set c = Range("A1:A" & LastRow).Find(what:=cell.Value, _
    LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
    [a65536].End(xlUp) = Int((upr - lwr + 1) * Rnd + lwr) & "8" _
    & Int((upr2 - lwr2 + 1) * Rnd + lwr2) & "1"
    GoTo test
End If
Set myrng = Nothing
Set c = Nothing
1:
Next cell
Application.ScreenUpdating = True
End Sub

Put this in a dedicated normal module. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-07 19:14
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
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