Generating random numbers from a given range

[Darren]

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?

 

[Gary''s Student]

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

 

[Ryan H]

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.

 

[Phil Hibbs]

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 = RANDBETWEEN(1, 24)
DO
n2 = RANDBETWEEN(1, 24)
WHILE n2 = n1
DO
n3 = RANDBETWEEN(1, 24)
WHILE n3 = n1 OR n3 = n2
DO
n4 = RANDBETWEEN(1, 24)
WHILE n4 = n1 OR n4 = n2 OR n4 = n3

Phil Hibbs.

 

[Darren]

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.

 

[Phil Hibbs]

Sorry, those should be LOOP WHILE ...

 

[Phil Hibbs]

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.

 

[Peter T]

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

 

[Ryan H]

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

 

[Bernd P]

Hello Darren,

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

Regards,
Bernd

 

[Darren]

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.

 

[Darren]

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?

 

[Darren]

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