Random Numbers

R

robert morris

I need help with a VBA for creating NON-REPEATING random numbers between 0-9
in Col B4:B13 and Row C3:L3

Bob
 
J

Jarek Kujawa

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
 
J

Jim Cone

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"
<[email protected]>
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
 
R

robert morris

Jim,

I need random numbers for BOTH Row 3 and Col B.

Have I answered your question correctly?

Bob
 
R

robert morris

Jarek,

Thanks, your Code works perfectly for the Column B but does not address Row 3.

Bob
 
J

Jarek Kujawa

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?
 
R

robert morris

Jarek,

Yes, populate both ranges separately. Possibly two different Codes?

I appreciate your help.

Bob
 
R

robert morris

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
 
J

Jarek Kujawa

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?
 
R

robert morris

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
 
J

Jim Cone

Change .Clear to .ClearContents
--
Jim Cone
Portland, Oregon USA


"robert morris"
<[email protected]>
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
 
J

Jack Sons

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
 
Top