On Sep 10, 8:13*am, "joeu2004" wrote:
"Paul Black" wrote:
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.

You almost got it right. *A number of small mistakes. *Instead of explaining
each one, I suggest that you simply use the macro below.

Instead of putting the macro into a module and relaying on
Worksheets("Sheet1").Select to ensure that the correct worksheet is
modified, I suggest that you put the macro in the worksheet object. *That
allows you to rename the worksheet without having to change the macro.

To do that, right-click on the worksheet tab at the bottom of the Excel
window, click on View Code, then copy the macro and paste it into the VBE
pane in the VBA window. *Then you can close the VBA window, if you wish..

Programming note.... * This is not the most efficient implementation. *But
it might be easier to understand as is.

-----

Option Explicit

Sub Shuffle()

' ***** customize*****
Const nMax As Long = 49
Const rAddress As String = "b2:g10"
Const clrAddress As String = "b:k"
' *****

Dim i As Long, j As Long, n As Long
Dim r As Range

' change #If 0 to #If 1 to generate same random
' sequence each time for debugging purposes
#If 0 Then
* * i = Rnd(-1)
* * Randomize 1
#Else
* * Randomize
#End If

Set r = Range(rAddress)

' clear any previous data
Columns(clrAddress).ClearContents

' generate up to nMax random numbers.
' generate fewer if range is smaller than nMax
n = IIf(nMax <= r.Count, nMax, r.Count)

' initialize set of random numbers, 1 to nMax
ReDim num(1 To n) As Long
For i = 1 To n: num(i) = i: Next

For i = 1 To n
* * ' generate next random number.
* * ' store into range, across columns,
* * ' then down rows
* * j = 1 + Int(n * Rnd())
* * r(i) = num(j)

* * ' remove num(j) from set of random numbers
* * If j < n Then num(j) = num(n)
* * n = n - 1
Next

End Sub

Hi Joe, excellent, THANK YOU.
I will go through your code and get a better understanding of what is
happening.

Kind regards,
Paul