Unique Random Numbers
On Sep 9, 3:56*am, isabelle wrote:
hello Paul,
do you use excel to do this?
if so, why not use table with "ReDim Preserve"
and check the contents with *"Application.Match"
dont forget, you can set a table outside of the macro to maintain content and then change this content.
--
isabelle
Le 2011-09-08 19:07, Paul Black a écrit :
- Show quoted text -
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
|