#### Generating random numbers from a given range

```I want to generate 4 random numbers from a range of 1 through to 24. Each
number has to be different from the other 3 though. Is there a way to do
this?
``` 0  Utf
2/2/2010 4:11:01 PM excel.programming  6508 articles. 2 followers. 12 Replies 1043 Views Similar Articles

[PageSpeed] 5

```How about:

Sub pickum()
v1 = randbetween(1, 6)
v1 = v1 & Chr(10) & randbetween(7, 12)
v1 = v1 & Chr(10) & randbetween(13, 18)
v1 = v1 & Chr(10) & randbetween(19, 24)
MsgBox v1
End Sub

--
Gary''s Student - gsnu201001

"Darren" wrote:

> I want to generate 4 random numbers from a range of 1 through to 24. Each
> number has to be different from the other 3 though. Is there a way to do
> this?
``` 0  Utf
2/2/2010 4:35:01 PM
```I wasn't sure where you wanted the random number values to be stored.  I
assumed it was in a range so I put the values in A1:A4.  Did you want them in
an array?

Option Explicit

Sub ProduceRandomNumbers()

Dim MyRange As Range
Dim cell As Range

Set MyRange = Range("A1:A4")

RunAgain:

For Each cell In MyRange
cell.Value = Int((24 * Rnd) + 1)
Next cell

For Each cell In MyRange
If WorksheetFunction.CountIf(MyRange, cell.Value) > 1 Then
GoTo RunAgain
End If
Next cell

End Sub

Hope this helps!  If so, let me know, click "YES" below.
--
Cheers,
Ryan

"Darren" wrote:

> I want to generate 4 random numbers from a range of 1 through to 24. Each
> number has to be different from the other 3 though. Is there a way to do
> this?
``` 0  Utf
2/2/2010 4:58:11 PM
```On Feb 2, 4:11=A0pm, Darren <Dar...@discussions.microsoft.com> wrote:
> I want to generate 4 random numbers from a range of 1 through to 24. Each
> number has to be different from the other 3 though. Is there a way to do
> this?

RANDBETWEEN is in the Analysis ToolPak AddIn, install that first.

n1 =3D RANDBETWEEN(1, 24)
DO
n2 =3D RANDBETWEEN(1, 24)
WHILE n2 =3D n1
DO
n3 =3D RANDBETWEEN(1, 24)
WHILE n3 =3D n1 OR n3 =3D n2
DO
n4 =3D RANDBETWEEN(1, 24)
WHILE n4 =3D n1 OR n4 =3D n2 OR n4 =3D n3

Phil Hibbs.
``` 0  Phil
2/2/2010 4:59:29 PM
```Thankyou for the prompt reply. Now that I have my random list of 4, I want to
find these values from within cells D1:AA1 and copy the corresponding lines
of data contained in *7:*106 below the randomly generated sequence, which
subsequently now goes horizontally.

"Gary''s Student" wrote:

>
> Sub pickum()
> v1 = randbetween(1, 6)
> v1 = v1 & Chr(10) & randbetween(7, 12)
> v1 = v1 & Chr(10) & randbetween(13, 18)
> v1 = v1 & Chr(10) & randbetween(19, 24)
> MsgBox v1
> End Sub
>
> --
> Gary''s Student - gsnu201001
>
>
> "Darren" wrote:
>
> > I want to generate 4 random numbers from a range of 1 through to 24. Each
> > number has to be different from the other 3 though. Is there a way to do
> > this?
``` 0  Utf
2/2/2010 5:01:01 PM
```Sorry, those should be LOOP WHILE ...
``` 0  Phil
2/2/2010 5:13:42 PM
```Darren wrote:
> Thankyou for the prompt reply. Now that I have my random list of 4, I want to
> find these values from within cells D1:AA1 and copy the corresponding lines
> of data contained in *7:*106 below the randomly generated sequence, which
> subsequently now goes horizontally.

I don't understand.

Phil Hibbs.
``` 0  Phil
2/2/2010 5:15:21 PM
```Another way -

In A1:24 enter the formula =RAND()
Name A1:A24 "Rands"
In B1
=MATCH(SMALL(Rands,1),Rands,0)
in B2:B4 similar formula but change the 1 to 2, 3 & 4 respectively

If you don't want new random numbers on each re-calc, put the rand()
formulas elsewhere and copy to A1:A24 (or the named range) as values when
you want a new set of unique random numbers.

Regards,
Peter T

"Darren" <Darren@discussions.microsoft.com> wrote in message
news:61F10F6B-4AB4-45ED-9BFF-8ED2490F5204@microsoft.com...
>I want to generate 4 random numbers from a range of 1 through to 24. Each
> number has to be different from the other 3 though. Is there a way to do
> this?

``` 0  Peter
2/2/2010 5:18:46 PM
```This is untested, but give it a shot

Sub ProduceRandomNumbers()

Dim i As Long
Dim MyNumber As Long
Dim MyNumbers(0 To 3) As Long
Dim n As Long
Dim FoundRange As Range
Dim c As Long
Dim MyRange As Range

RunAgain:

' assign random numbers to array
For i = 0 To 3
MyNumbers(i) = Int((24 * Rnd) + 1)
Next i

' check for duplicates
For i = 0 To 3
n = 0
MyNumber = MyNumbers(i)
If MyNumber = MyNumbers(i) Then
n = n + 1
If n > 1 Then GoTo RunAgain
End If
Next i

For i = 0 To 3

Set FoundRange = Range("D1:AA1").Find(What:=MyNumbers(i))

c = FoundRange.Column
Set MyRange = Union(Range(Cells(7, c), Cells(106, c)), MyRange)
End If
Next i

' copy range to new sheet
Sheets("Sheet2").Range("A1") = MyRange

End Sub
--
Cheers,
Ryan

"Darren" wrote:

> Thankyou for the prompt reply. Now that I have my random list of 4, I want to
> find these values from within cells D1:AA1 and copy the corresponding lines
> of data contained in *7:*106 below the randomly generated sequence, which
> subsequently now goes horizontally.
>
> "Gary''s Student" wrote:
>
> >
> > Sub pickum()
> > v1 = randbetween(1, 6)
> > v1 = v1 & Chr(10) & randbetween(7, 12)
> > v1 = v1 & Chr(10) & randbetween(13, 18)
> > v1 = v1 & Chr(10) & randbetween(19, 24)
> > MsgBox v1
> > End Sub
> >
> > --
> > Gary''s Student - gsnu201001
> >
> >
> > "Darren" wrote:
> >
> > > I want to generate 4 random numbers from a range of 1 through to 24. Each
> > > number has to be different from the other 3 though. Is there a way to do
> > > this?
``` 0  Utf
2/2/2010 5:29:01 PM
```Hello Darren,

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

Regards,
Bernd
``` 0  Bernd
2/2/2010 5:39:15 PM
```Thankyou Ryan, but this is heading away from where I want to go. I have since
replaced the 4 random numbers with 4 Skills from a list of 24. My actual
chart has a list of names in A7:A106. The skills are in cells D1:AA1. I want
to find the generated skill name from within D1:AA1, lets say for example the
1st random skill corresponds to the skill in H1. I now want to copy The
values from H7:H106 under the 1st randomly generated skill. Same for the 2nd,
3rd and 4th.

I hope I made this clearer.

"Ryan H" wrote:

> This is untested, but give it a shot
>
>
> Sub ProduceRandomNumbers()
>
> Dim i As Long
> Dim MyNumber As Long
> Dim MyNumbers(0 To 3) As Long
> Dim n As Long
> Dim FoundRange As Range
> Dim c As Long
> Dim MyRange As Range
>
> RunAgain:
>
>     ' assign random numbers to array
>     For i = 0 To 3
>         MyNumbers(i) = Int((24 * Rnd) + 1)
>     Next i
>
>     ' check for duplicates
>     For i = 0 To 3
>         n = 0
>         MyNumber = MyNumbers(i)
>         If MyNumber = MyNumbers(i) Then
>             n = n + 1
>             If n > 1 Then GoTo RunAgain
>         End If
>     Next i
>
>     ' find number in header
>     For i = 0 To 3
>
>         Set FoundRange = Range("D1:AA1").Find(What:=MyNumbers(i))
>
>             c = FoundRange.Column
>             Set MyRange = Union(Range(Cells(7, c), Cells(106, c)), MyRange)
>         End If
>     Next i
>
>     ' copy range to new sheet
>     Sheets("Sheet2").Range("A1") = MyRange
>
> End Sub
> --
> Cheers,
> Ryan
>
>
> "Darren" wrote:
>
> > Thankyou for the prompt reply. Now that I have my random list of 4, I want to
> > find these values from within cells D1:AA1 and copy the corresponding lines
> > of data contained in *7:*106 below the randomly generated sequence, which
> > subsequently now goes horizontally.
> >
> > "Gary''s Student" wrote:
> >
> > >
> > > Sub pickum()
> > > v1 = randbetween(1, 6)
> > > v1 = v1 & Chr(10) & randbetween(7, 12)
> > > v1 = v1 & Chr(10) & randbetween(13, 18)
> > > v1 = v1 & Chr(10) & randbetween(19, 24)
> > > MsgBox v1
> > > End Sub
> > >
> > > --
> > > Gary''s Student - gsnu201001
> > >
> > >
> > > "Darren" wrote:
> > >
> > > > I want to generate 4 random numbers from a range of 1 through to 24. Each
> > > > number has to be different from the other 3 though. Is there a way to do
> > > > this?
``` 0  Utf
2/2/2010 5:43:03 PM
```Thankyou all for your help. Unfortunately we are heading in the wrong
direction. I have my randon list which is now text not numbers. Taking only
the 1st random answer, this is what I want to do:

My chart has names in A7:A78, skills in D1:AA1.
Using RAND() and indexing a separate vertical list of the skills I can
randomly generate 4 from the list. These are placed in B160:E160 with the
list of names copied to A161:A232.

I want to find the 1st randomly generated skill (B160) from within D1:AA1
then copy the corresponding data from below it (*7:*78) into B161:B232.

For example:
1st generated skill is archery. I want to find archery in D1:AA1 which is
actually H1 then copy H7:H78 into B161:B232.

``` 0  Utf
2/2/2010 6:43:01 PM
```Figured it out.

in cell C161 I have =INDEX(\$D7:\$AA7,MATCH(C\$160,\$D\$1:\$AA\$1,0)) then copy it
across 4 columns and down to C232:F232
``` 0  Utf
2/2/2010 7:18:10 PM Similar Artilces: