Unique Random Numbers
On Sep 9, 4:00*pm, Paul Black wrote:
On Sep 9, 3:51*pm, isabelle wrote:
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- Hide quoted text -
- Show quoted text -
Thanks Isabelle but it still does not work.
Kind regards,
Paul- Hide quoted text -
- Show quoted text -
One other thing.
If I do not use the "On Error Resume Next" I get a Run-time error '9',
is this something to do with defining the Array or Variables.
Thanks again,
Paul
|