Modify working code to expand the output.

[L. Howard]

This first code works excellent.

The code below it is my attempt to modify that working code to produce six 'blocks' one below the next, with the random sorted numbers incremented by 20 for each column to continue on to the last block.

Starts at 1 - 21 in first block first column so the last column in the sixth block would be 221 - 276. (hope I got the math correct)

The six lines that are commented out in the second code are the rows and MyCol is the column the subsequent blocks should be in.

I am trying to increment rowUp and rowDn for the WorksheetFunction.Transpose(b)
output. As it is the output is strange and I cannot figure how or where to make my adjustments.

Thanks,
Howard


Sub Randomizer_Description_6x20()
'/ by Claus

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
Dim mC As Long

' Must Name the Sheet and Range for Cleaing Randomized Results.
'Sheets("Description Builder").Range("A2:A127").ClearContents
Application.ScreenUpdating = False

'1 to 6 = Number of Times to run Randomizer
For mC = 1 To 6

Small = 1
Big = 20

' 3 to 13 step 2 = Column 3 or C, Column 13 or M.

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 = 0 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

' This is the range of the concatenated results / Sheet must be named here.
' The "B" assigns the column for special paste
' The (2) allows no blank rows between the pastespecial.
'Range("P221").Copy
'Sheets("Description Builder").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Next 'mC
Application.CutCopyMode = True
Application.ScreenUpdating = True
Beep
End Sub


Sub Exp_Randomizer_Description_6x20()
'/ by Claus (modified)

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
Dim mC As Long
Dim rowUp As Long, rowDn As Long

' Must Name the Sheet and Range for Cleaing Randomized Results.
'Sheets("Sheet1").Range("A2:A127").ClearContents
Application.ScreenUpdating = False

'1 to 6 = Number of Times to run Randomizer
For mC = 1 To 36
Range("A1") = Range("A1") + 1

' Small = The first cell ( 1 ) Big = The last cell ( 20 ) of the Title Data.
Small = 1
Big = 20
rowUp = 2
rowDn = 21

' 3 to 13 step 2 = Column 3 or C, Column 13 or O.

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 = 0 To d
e = Int(d * Rnd) + 1
f = b(c): b(c) = b(e): b(e) = f
Next

Range(Cells(rowUp, myCol), Cells(rowDn, myCol)) = _
WorksheetFunction.Transpose(b)

'Range(Cells(2, myCol), Cells(21, myCol)) = _
WorksheetFunction.Transpose(b)

'Range(Cells(24, myCol), Cells(43, myCol)) = _
WorksheetFunction.Transpose(b)

'Range(Cells(46, myCol), Cells(65, myCol)) = _
WorksheetFunction.Transpose(b)

'Range(Cells(68, myCol), Cells(87, myCol)) = _
WorksheetFunction.Transpose(b)

'Range(Cells(90, myCol), Cells(109, myCol)) = _
WorksheetFunction.Transpose(b)

'Range(Cells(112, myCol), Cells(131, myCol)) = _
WorksheetFunction.Transpose(b)

Small = Small + 20
Big = Big + 20
rowUp = rowUp + 22
rowDn = rowDn + 22
Next

' This is the range of the concatenated results / Sheet must be named here.
' The "B" assigns the column for special paste
' The (2) allows no blank rows between the pastespecial.

' Range("P221").Copy
'Sheets("Description Builder").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Next 'mC
Application.CutCopyMode = True
Application.ScreenUpdating = True
Beep
End Sub

 

[Claus Busch]

Hi Howard,

Am Wed, 12 Mar 2014 23:38:09 -0700 (PDT) schrieb L. Howard:

Starts at 1 - 21 in first block first column so the last column in the sixth block would be 221 - 276. (hope I got the math correct)

I don't know if i really understood your question.
Please try following code and adapt it to your wishes:

Sub Randomizer()
Dim a(19) As Variant, b, c, d, e, f
Dim Small As Integer, Big As Integer
Dim i As Long, j As Long, n As Long, k As Long
Dim MyCol As Long, myRow As Long


MyCol = 1
For n = 1 To 101 Step 20
Small = n
Big = Small + 19
j = 0
For i = Small To Big
a(j) = i
j = j + 1
Next
b = a: Randomize
d = UBound(b)
For c = 0 To d
e = Int(d * Rnd) + 1
f = b(c): b(c) = b(e): b(e) = f
Next

Cells(n + k, MyCol) _
.Resize(rowsize:=20) = WorksheetFunction.Transpose(b)
k = k + 1
Next
End Sub


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Wed, 12 Mar 2014 23:38:09 -0700 (PDT) schrieb L. Howard:






I don't know if i really understood your question.

Please try following code and adapt it to your wishes:



Sub Randomizer()

Dim a(19) As Variant, b, c, d, e, f

Dim Small As Integer, Big As Integer

Dim i As Long, j As Long, n As Long, k As Long

Dim MyCol As Long, myRow As Long





MyCol = 1

For n = 1 To 101 Step 20

Small = n

Big = Small + 19

j = 0

For i = Small To Big

a(j) = i

j = j + 1

Next

b = a: Randomize

d = UBound(b)

For c = 0 To d

e = Int(d * Rnd) + 1

f = b(c): b(c) = b(e): b(e) = f

Next



Cells(n + k, MyCol) _

.Resize(rowsize:=20) = WorksheetFunction.Transpose(b)

k = k + 1

Next

End Sub





Regards

Claus B.

Hi Claus, thanks for taking a look.

The code did not do as I need and I am just as lost to adapt it as the other original working code.

Here is a link with the format I need. The set of randomly placed numbers in the block with the border is what your original code returns, which is great.

https://www.dropbox.com/s/gjiom5mm30dklbp/Book3 Example Format.xlsm

My need is for it to do the same with the other five blocks where you can see the numbers are in sequence. Each of those twenty numbers in all cases need to be randomly placed like the Bordered box with each running of the code.

Hope this makes more sense.

Thanks.
Howard

 

[Claus Busch]

Hi Howard,

Am Thu, 13 Mar 2014 02:39:19 -0700 (PDT) schrieb L. Howard:

https://www.dropbox.com/s/gjiom5mm30dklbp/Book3 Example Format.xlsm

got it.

Try:

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

Small = 1

For n = 2 To 112 Step 22
For k = 3 To 13 Step 2
Big = Small + 19
j = 0
For i = Small To Big
a(j) = i
j = j + 1
Next
b = a: Randomize
d = UBound(b)
For c = 0 To d
e = Int(d * Rnd) + 1
f = b(c): b(c) = b(e): b(e) = f
Next
Cells(n, k).Resize(rowsize:=20) = WorksheetFunction.Transpose(b)
Small = Small + 20
Next
Next
End Sub


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Thu, 13 Mar 2014 02:39:19 -0700 (PDT) schrieb L. Howard:






got it.



Try:



Sub Randomizer()

Dim a(19) As Variant, b, c, d, e, f

Dim Small As Integer, Big As Integer

Dim i As Long, j As Long, n As Long, k As Long



Small = 1



For n = 2 To 112 Step 22

For k = 3 To 13 Step 2

Big = Small + 19

j = 0

For i = Small To Big

a(j) = i

j = j + 1

Next

b = a: Randomize

d = UBound(b)

For c = 0 To d

e = Int(d * Rnd) + 1

f = b(c): b(c) = b(e): b(e) = f

Next

Cells(n, k).Resize(rowsize:=20) = WorksheetFunction.Transpose(b)

Small = Small + 20

Next

Next

End Sub





Regards

Claus B.

Now that is a work of art!! Not only does it do MORE but there is less code.

Thanks, Claus. Never cease to amaze.

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Thu, 13 Mar 2014 04:30:08 -0700 (PDT) schrieb L. Howard:

Now that is a work of art!! Not only does it do MORE but there is less code.

always glad to help
First the output goes from column 3 to 13 each second column. Then the
same thing is repeated 22 rows down and so on.
If you recognize the regularities in the structure of a table it is easy
to write a loop.


Regards
Claus B.