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

Hi Ozgur,

Try changing:

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


to

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


---
Regards,
Norman


"Ozgur Pars" wrote in message
...
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

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)

"Ozgur Pars" wrote in message
...
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

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)

"Bob Phillips" wrote in message
...
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)

"Ozgur Pars" wrote in message
...
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

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


"Bob Phillips" wrote:

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)

"Ozgur Pars" wrote in message
...
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

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" wrote:

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)

"Bob Phillips" wrote in message
...
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)

"Ozgur Pars" wrote in message
...
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

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)

"Ozgur Pars" wrote in message
...
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


"Bob Phillips" wrote:

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)

"Ozgur Pars" wrote in message
...
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

Hello,

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

HTH,
Bernd