Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Six columns randon no.'s no dupes
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 whe 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Six columns randon no.'s no dupes
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 whe 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 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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Six columns randon no.'s no dupes
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 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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Six columns randon no.'s no dupes
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 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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Six columns randon no.'s no dupes
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 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. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Six columns randon no.'s no dupes
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. 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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Six columns randon no.'s no dupes
On Thursday, March 6, 2014 3:31:59 AM UTC-8, Claus Busch wrote:
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. 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. -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Randon Selection of Items within Worksheet | Excel Worksheet Functions | |||
Randon selection of a value | Excel Discussion (Misc queries) | |||
Compare columns for dupes | Excel Programming | |||
Randon file saves | Excel Discussion (Misc queries) | |||
Randon Number Generation - HELP! | Excel Programming |