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. Follow

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:

> 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 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
    
    ' find number in header
    For i = 0 To 3
        
        Set FoundRange = Range("D1:AA1").Find(What:=MyNumbers(i))
        
        If Not FoundRange Is Nothing Then
            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:
> 
> > 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 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))
>         
>         If Not FoundRange Is Nothing Then
>             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:
> > 
> > > 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 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. 

How would I go about this?
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
Reply:

Similar Artilces: