If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|