Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() 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("P2:P21").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("P2:P21").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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Thursday, March 13, 2014 12:32:38 AM UTC-7, Claus Busch wrote:
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. 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/gjiom5mm30...%20Format.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 |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi Howard,
Am Thu, 13 Mar 2014 02:39:19 -0700 (PDT) schrieb L. Howard: https://www.dropbox.com/s/gjiom5mm30...%20Format.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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Thursday, March 13, 2014 3:59:50 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 13 Mar 2014 02:39:19 -0700 (PDT) schrieb L. Howard: https://www.dropbox.com/s/gjiom5mm30...%20Format.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. 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 |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating my code? | Excel Programming | |||
How do I modify the default working days in excel | Excel Discussion (Misc queries) | |||
Modify the official None-working days in the "NETWORKDAYS" Functio | Excel Worksheet Functions | |||
Modify sub to output to new sheet instead of via msg boxes | Excel Programming | |||
Output while working? | Excel Programming |