Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
would this macro help?
Sub cus() Dim cell As Range With Range("B4:B13") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Bob,
I think your requirements are a little too strict. <g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Jim,
I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. <g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Jarek,
Thanks, your Code works perfectly for the Column B but does not address Row 3. Bob "Jarek Kujawa" wrote: would this macro help? Sub cus() Dim cell As Range With Range("B4:B13") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
having re-read the conditions I have to agree with Jim
they are to strict are you trying to populate C3:L3 AND B4:B13 with random integers ranging from 0 to 9? if so I cannot provide a solution or you meant to populate both ranges SEPARATELY? On 16 Maj, 07:41, robert morris wrote: Jarek, Thanks, your Code works perfectly for the Column B but does not address Row 3. Bob "Jarek Kujawa" wrote: would this macro help? Sub cus() Dim cell As Range With Range("B4:B13") ..Clear Â* Â* For Each cell In .Cells repeat: Â* Â* k = WorksheetFunction.RandBetween(0, 9) Â* Â* Â* Â* If WorksheetFunction.CountIf(.Cells, k) = 0 Then Â* Â* Â* Â* Â* Â* cell = k Â* Â* Â* Â* Else Â* Â* Â* Â* Â* Â* GoTo repeat Â* Â* Â* Â* End If Â* Â* Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Jarek,
Yes, populate both ranges separately. Possibly two different Codes? I appreciate your help. Bob "Jarek Kujawa" wrote: having re-read the conditions I have to agree with Jim they are to strict are you trying to populate C3:L3 AND B4:B13 with random integers ranging from 0 to 9? if so I cannot provide a solution or you meant to populate both ranges SEPARATELY? On 16 Maj, 07:41, robert morris wrote: Jarek, Thanks, your Code works perfectly for the Column B but does not address Row 3. Bob "Jarek Kujawa" wrote: would this macro help? Sub cus() Dim cell As Range With Range("B4:B13") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
i.e. for row 3 from 0 to 9 and for col B from 0 to 9?
On 16 Maj, 07:39, robert morris wrote: Jim, I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. <g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon Â*USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Jarek,
Yes, random numbers between 0-9, Col Range B4:B13 and random numbers between 0-9, Row Range C3:L3. As I said, could be two codes with Buttons. Bob "Jarek Kujawa" wrote: i.e. for row 3 from 0 to 9 and for col B from 0 to 9? On 16 Maj, 07:39, robert morris wrote: Jim, I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. <g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
for C3:L3
Sub cus2() Dim cell As Range With Range("C3:L3") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub then assign cus to Button1 and cus2 to Button2 is this ok? On 16 Maj, 08:55, robert morris wrote: Jarek, Yes, random numbers between 0-9, Col Range B4:B13 and random numbers between 0-9, Row Range C3:L3. As I said, could be two codes with Buttons. Bob "Jarek Kujawa" wrote: i.e. for row 3 from 0 to 9 and for col B from 0 to 9? On 16 Maj, 07:39, robert morris wrote: Jim, I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. <g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon Â*USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Jarek,
Works almost perfectly. Random Numbers are perfect however, when applied, I lose my Formatting for the "Range" on both the Col and Row. Why would this happen? Sorry for my delay in replying, I fell asleep. Bob "Jarek Kujawa" wrote: for C3:L3 Sub cus2() Dim cell As Range With Range("C3:L3") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub then assign cus to Button1 and cus2 to Button2 is this ok? On 16 Maj, 08:55, robert morris wrote: Jarek, Yes, random numbers between 0-9, Col Range B4:B13 and random numbers between 0-9, Row Range C3:L3. As I said, could be two codes with Buttons. Bob "Jarek Kujawa" wrote: i.e. for row 3 from 0 to 9 and for col B from 0 to 9? On 16 Maj, 07:39, robert morris wrote: Jim, I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. <g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
See http://www.cpearson.com/Excel/randomNumbers.aspx and
http://www.cpearson.com/Excel/ShuffleArray.aspx for code that will do this, among other things. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 15 May 2009 20:47:01 -0700, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Change .Clear to .ClearContents
-- Jim Cone Portland, Oregon USA "robert morris" wrote in message Jarek, Works almost perfectly. Random Numbers are perfect however, when applied, I lose my Formatting for the "Range" on both the Col and Row. Why would this happen? Sorry for my delay in replying, I fell asleep. Bob |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Jim,
That was it! Thanks Bob "Jim Cone" wrote: Change .Clear to .ClearContents -- Jim Cone Portland, Oregon USA "robert morris" wrote in message Jarek, Works almost perfectly. Random Numbers are perfect however, when applied, I lose my Formatting for the "Range" on both the Col and Row. Why would this happen? Sorry for my delay in replying, I fell asleep. Bob |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Numbers
Use this:
Sub FillRand() Dim nums() As Integer Dim maxval As Integer Dim nrows As Integer, ncols As Integer Dim j As Integer, k As Integer Dim Ptr As Integer Randomize Set s = Selection maxval = s.Cells.Count nrows = s.Rows.Count ncols = s.Columns.Count ReDim nums(maxval, 2) 'Fill the initial array For j = 1 To maxval nums(j, 1) = j nums(j, 2) = Int((Rnd * maxval) + 1) Next j 'Sort the array based on the random numbers For j = 1 To maxval - 1 Ptr = j For k = j + 1 To maxval If nums(Ptr, 2) nums(k, 2) Then Ptr = k Next k If Ptr < j Then k = nums(Ptr, 1) nums(Ptr, 1) = nums(j, 1) nums(j, 1) = k k = nums(Ptr, 2) nums(Ptr, 2) = nums(j, 2) nums(j, 2) = k End If Next j 'Fill in the cells Ptr = 0 For j = 1 To nrows For k = 1 To ncols Ptr = Ptr + 1 s.Cells(j, k) = nums(Ptr, 1) Next k Next jEnd SubThis macro uses a two-dimensional array (nums) to figure out which numbers to use and the order in which they should be used. Near the beginning of the macro the array is filled with a static number (1 through the number of cells) and a random number between 1 and the number of cells. This second number is then used to sort the array. Once the array is stored, it is a simple matter to place the original numbers in the cells. By the way, the reason a two-dimensional array is used is because the Rnd function that VBA uses to generate random numbers can return duplicate values. Thus, even through the second dimension of the array can have duplicates in it, when the array is finally sorted, the first dimension will not have duplicates. To use the macro, start by selecting the cells you want to have filled with sequential values in a random order. When you run the macro, that range is filled. For instance, if you select ten cells and then run the macro, then those cells are filled with the numbers 1 through 10, in random order. Jack Sons "robert morris" schreef in bericht ... I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting numbers divisible by 4 from random numbers in ascending order | Excel Worksheet Functions | |||
How to select top six numbers from a of range of random numbers | Excel Worksheet Functions | |||
Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |