Six columns randon no.'s no dupes

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

I have modified this code by Jim R. to do most of what I want.

Place 20 numbers in six columns at random with no dupes where:

Col C 1 to 20
Col E 21 to 40
Col G 41 to 60
Col I 61 to 80
Col K 81 to 100
Col M 101 to 120

Column C numbers are fine, 1 to 20 randomly from C2 to C21.
All the other column do the same except the code puts a 0 (zero) where the max number for that column should be.

So for column K for example, I get 81 to 99 and a 0 (zero) all and randomly placed each time I run the code.

The Msgbox shows the correct Small and Big numbers as the code loops, exactly like the pattern above for each loop.

At a loss.

Thanks,
Howard


Option Explicit

Sub Columns_CEGIKM() 'Rothstein, Jim
Application.ScreenUpdating = False

Dim X As Long, Small As Long, Big As Long, Index As Long, Temp As Long, Numbers() As Long
Dim NumberOfRandoms As Long
Dim i As Long
Dim MyCol As Long

Small = 1
Big = 20
MyCol = 3

On Error Resume Next

For i = 1 To 6
MsgBox Small & " " & Big
NumberOfRandoms = Big

' Load up an array with all the values for the range of random numbers
ReDim Numbers(1 To Big - Small + 1)
For X = Small To Big
Index = Index + 1
Numbers(Index) = X

Next

' Randomly mix up the values in the array
For X = UBound(Numbers) To LBound(Numbers) Step -1
Index = Int((X - LBound(Numbers) + 1) * Rnd + LBound(Numbers))
Temp = Numbers(Index)
Numbers(Index) = Numbers(X)
Numbers(X) = Temp
Next

' Output the number of randoms specified by the NumberOfRandoms constant to the worksheet
For X = 1 To NumberOfRandoms
'/ starts list in C2 and down
Cells(X + 1, MyCol).Value = Numbers(X)
Next

MyCol = MyCol + 2
Small = Small + 20
Big = Big + 20

Next 'i
Application.ScreenUpdating = True

End Sub
 
hi Howard,

Am Thu, 6 Mar 2014 00:21:40 -0800 (PST) schrieb L. Howard:
I have modified this code by Jim R. to do most of what I want.

Place 20 numbers in six columns at random with no dupes where:

Col C 1 to 20
Col E 21 to 40
Col G 41 to 60
Col I 61 to 80
Col K 81 to 100
Col M 101 to 120
Click to expand...

try:

Sub MixThem()
Dim a(19) As Variant, b, c, d, e, f
Dim Small As Integer, Big As Integer
Dim i As Long, j As Long, myCol As Long


Small = 1
Big = 20
For myCol = 3 To 13 Step 2
j = 0
For i = Small To Big
a(j) = i
j = j + 1
Next
b = a: Randomize
d = UBound(b)
For c = 1 To d
e = Int(d * Rnd) + 1
f = b(c): b(c) = b(e): b(e) = f
Next
Range(Cells(2, myCol), Cells(21, myCol)) = _
WorksheetFunction.Transpose(b)
Small = Small + 20
Big = Big + 20
Next
End Sub


Regards
Claus B.
 
Hi Howard,

Am Thu, 6 Mar 2014 10:59:40 +0100 schrieb Claus Busch:
For c = 1 To d
e = Int(d * Rnd) + 1
f = b(c): b(c) = b(e): b(e) = f
Next
Click to expand...

change the above to:
For c = 0 To d
e = Int(d * Rnd) + 1
f = b(c): b(c) = b(e): b(e) = f
Next


Regards
Claus B.
 
Hi Howard,

Am Thu, 6 Mar 2014 00:21:40 -0800 (PST) schrieb L. Howard:
For i = 1 To 6
MsgBox Small & " " & Big
NumberOfRandoms = Big

' Load up an array with all the values for the range of random numbers
ReDim Numbers(1 To Big - Small + 1)
For X = Small To Big
Index = Index + 1
Numbers(Index) = X

Next
Click to expand...

change the above to:

For i = 1 To 6
MsgBox Small & " " & Big
NumberOfRandoms = Big - Small + 1

' Load up an array with all the values for the range of random numbers
For X = Small To Big
Numbers(Index) = X
Index = Index + 1
Next

So Numbers has always 20 items decare it:
dim Numbers(19) As Long


Regards
Claus B.
 
change the above to:



For i = 1 To 6

MsgBox Small & " " & Big

NumberOfRandoms = Big - Small + 1



' Load up an array with all the values for the range of random numbers

For X = Small To Big

Numbers(Index) = X

Index = Index + 1

Next



So Numbers has always 20 items decare it:

dim Numbers(19) As Long





Regards

Claus B.
Click to expand...


Hi Claus,

Thanks for the help.

I ran the first code and it seems to work well.

I made the first change as you suggested and the code run just as well.

I can't figure out where the last change is to go in the code. What it is supposed to replace I don't see anywhere in the code.

I am using =SUM(...) at the bottom of each column to check for dupes (improper sum for that column) and running it about 50 times has me convinced it is working well without the last change.

Seems rock solid to me. Is the last change necessary?

Howard
 
Hi Howard,

Am Thu, 6 Mar 2014 03:27:47 -0800 (PST) schrieb L. Howard:
I can't figure out where the last change is to go in the code. What it is supposed to replace I don't see anywhere in the code.
Click to expand...

the last change is a change in Ricks code. You didn't fill the array
properly. A 1D-array starts with index 0. You started at 1. So item 0
was 0


Regards
Claus B.
 
Hi Howard,



Am Thu, 6 Mar 2014 03:27:47 -0800 (PST) schrieb L. Howard:






the last change is a change in Ricks code. You didn't fill the array

properly. A 1D-array starts with index 0. You started at 1. So item 0

was 0





Regards

Claus B.

--
Click to expand...


Okay, got it. The way I was using that code was to set an entire code up of a single column and then call the 6 versions in succession from the previous. It worked okay that way as far as not producing a 0 (zero) but I could not live with it in my mind, although it was pretty fast but way clunky.

Yours really does the trick.

Thanks much.

Regards,
Howard
 
Back
Top