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?