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

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

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

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

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

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 -

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 -

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 -

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 -

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 -

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 -

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

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

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

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