Creating Unique Random Numbers?

[Guest]

Hi everyone,
I found the below code on this board (I think it originated from Tom
Ogilvy). I need to put the generated numbers in a row but the list generated
is designed for a column (if this makes any sense at all).
Its probably simple but I am new at this stuff and would appreciate any help.

Rgds,
Ozgur

Sub GetThem()
Dim arrCheck(1 To 800) As Long
Dim arrList(1 To 799) As Long
Dim j As Long
Dim N As Long
Const LNG_PLUG As Long = 999

j = 1
Do While j < 4
'Get a random number
Randomize
N = Int(Rnd * 800 + 1)
'If number unique then add to arrList.
If arrCheck(N) <> LNG_PLUG Then
arrList(j) = N
arrCheck(N) = LNG_PLUG
j = j + 1
End If
Loop
Sheets(1).Range("A4:A6").Value = arrList()
' Sheets(1).Range(Cells(5, 1), Cells(5, Sheets(1).Range("b1"))).Value =
arrList()
End Sub

 

[Norman Jones]

Hi Ozgur,

Try changing:

Sheets(1).Range("A4:A6").Value = arrList()

to

Sheets(1).Range("A4").Resize(1, 3).Value = arrList()

 

[Bob Phillips]

Sub GetThem()
Dim j As Long
Dim N As Long
Const LNG_PLUG As Long = 999
Const NUM_GENERATED As Long = 799 '<=== change to suit
Dim arrCheck(1 To NUM_GENERATED + 1) As Long
Dim arrList(1 To NUM_GENERATED) As Long

j = 1
Do While j < NUM_GENERATED
'Get a random number
Randomize
N = Int(Rnd * NUM_GENERATED + 1)
'If number unique then add to arrList.
If arrCheck(N) <> LNG_PLUG Then
arrList(j) = N
arrCheck(N) = LNG_PLUG
j = j + 1
End If
Loop
Sheets(1).Range("A1:A" & NUM_GENERATED).Value =
Application.Transpose(arrList())
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Reading Norman's answer, I realise I read it back to front. But if you want
to generate more than 4 numbers, more than 256, you have a problem as you
only have 256 columns. Is that an issue?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob, Norman thanks for your replies.

Can I link the constant to a cell? Rather than inputting it into the code I
want it to change before every run. When I tried to do so I failed getting a
error message about the constant.
Const NUM_GENERATED As Long = Sheets("OFFERS").Range("BO2").Value '<===
change to suit

However I used application.Transpose(arrList()) with the previus code and
got the desired result.

Thank you for your help.

Best regards,
Ozgur

 

[Guest]

Bob,
thats exactly the problem. You got it right not the other way around. (or my
question was misleading)
I used your advice and Application.Transpose(arrList()) solved my problem.

Thank you,
Ozgur

 

[Bob Phillips]

Sure, try this

Sub GetThem()
Dim j As Long
Dim N As Long
Const LNG_PLUG As Long = 999
Dim NUM_GENERATED As Long
Dim arrCheck() As Long
Dim arrList() As Long

NUM_GENERATED = Sheets("OFFERS").Range("BO2").Value

ReDim arrCheck(1 To NUM_GENERATED + 1)
ReDim arrList(1 To NUM_GENERATED)

j = 1
Do While j < NUM_GENERATED
'Get a random number
Randomize
N = Int(Rnd * NUM_GENERATED + 1)
'If number unique then add to arrList.
If arrCheck(N) <> LNG_PLUG Then
arrList(j) = N
arrCheck(N) = LNG_PLUG
j = j + 1
End If
Loop
Sheets(1).Range("A1:A" & NUM_GENERATED).Value = _
Application.Transpose(arrList())
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bplumhoff]

Hello,

I suggest to take my UDF UniqRandInt:
http://www.sulprobil.com/html/uniqrandint.html

HTH,
Bernd