hi Paul,

Arrayrange is not equal to 49 but 54
you can change this line
LastNumber = 49
for
LastNumber = ActiveSheet.Range (Cells (2, 2), Cells (10, 7)). Count

--
isabelle


Le 2011-09-09 09:52, Paul Black a écrit :

Thanks for the reply Isabelle.
Unfortunately my knowledge of VBA is not that great.
I have managed however to put the following code together but
unfortunately it produces the final number 3 times for some reason.
Could you please have a look at it to see what I am doing wrong
please.
Here is the code ...

Sub Shuffle()
Dim Number()

Worksheets("Sheet1").Select

With ActiveSheet
.Columns("A:K").ClearContents
End With

On Error Resume Next

LastNumber = 49

Set ArrayRange = ActiveSheet.Range(Cells(2, 2), Cells(10, 7))

ReDim Number(LastNumber)

For i = 1 To LastNumber
Number(i) = i
Next i

For Each c In ArrayRange
Placement = Int(Rnd() * LastNumber + 1)
c.Value = Number(Placement)
dummy = Number(LastNumber)
Number(LastNumber) = Number(Placement)
Number(Placement) = dummy
LastNumber = LastNumber - 1
Next c

On Error GoTo 0
End Sub

Thanks,
Paul