Random number macro

[Victor Delta]

I would like to create an Excel macro which will put a series of random
integers (leaving values not the formulas) in cells in a column. It should
start in the currently selected cell and fill downwards until it has covered
the number of cells given by the number in, say, cell P1. The random
integers should be between 1 and the number in cell P2 (inclusive).

Can anyone help please?

Many thanks,

V

 

[Mike H]

Hi,

Try this

Sub standard()
Top = Range("P2").Value
For x = 1 To Range("P1").Value
ActiveCell.Offset(x - 1) = Int((Top - 1 + 1) * Rnd + 1)
Next
End Sub

Mike

 

[Lars-Åke Aspelin]

I would like to create an Excel macro which will put a series of random
integers (leaving values not the formulas) in cells in a column. It should
start in the currently selected cell and fill downwards until it has covered
the number of cells given by the number in, say, cell P1. The random
integers should be between 1 and the number in cell P2 (inclusive).

Can anyone help please?

Many thanks,

V

Try this macro:

Sub victor_delta()
For i = 1 To ActiveSheet.Range("P1").Value
ActiveCell.Offset(i - 1, 0).Value =
WorksheetFunction.RandBetween(1, ActiveSheet.Range("P2").Value)
Next i
End Sub

Hope this helps / Lars-Åke

 

[Mike H]

Hi,

It's kind of contradictory because as soom as you strat to intefere the
numbers cease to be random but this mod makes the numbers unique. You must
however give a big enough number range to allow them to be unique

Sub standard()
Dim fillrange As Range, top As Long, x As Long
Set fillrange = Range(ActiveCell.Address & ":" & _
ActiveCell.Offset(Range("P1").Value - 1).Address)
top = Range("P2").Value
For x = 1 To Range("P1").Value
Do
ActiveCell.Offset(x - 1) = Int((top - 1 + 1) * Rnd + 1)
Loop Until WorksheetFunction.CountIf(fillrange, _
ActiveCell.Offset(x - 1).Value) < 2
Next
End Sub

Mike

 

[Jim Cone]

In case you have the need, the free Special Randoms workbook;
Download here... http://excelusergroup.org/media/

Can generate:
1. Randoms that total.
2. Randoms that average.
3. Unique random numbers
4. Random text or numbers of a specified length

Constrained by Set Size, Min/Max value or length.
--
Jim Cone
Portland, Oregon USA




"Victor Delta" <[email protected]>
wrote in message
I would like to create an Excel macro which will put a series of random
integers (leaving values not the formulas) in cells in a column. It should
start in the currently selected cell and fill downwards until it has covered
the number of cells given by the number in, say, cell P1. The random
integers should be between 1 and the number in cell P2 (inclusive).

Can anyone help please?
Many thanks,
V

 

[Mike H]

Lars,

I have never been able to use RANDBETWEEN in the way you suggest and would
have to modify you macro thus to make it work, Can you really call it with
worksheetfunction? yes I do have all the relevent references set.

Sub victor_delta()
For i = 1 To ActiveSheet.Range("P1").Value
ActiveCell.Offset(i - 1) = Application.Run("ATPVBAEN.XLA!Randbetween",
1, Range("P2").Value)
Next i
End Sub

Mike

 

[Lars-Åke Aspelin]

I have only tested the macro I suggested in Excel 2007. There it seems
to work as expected. Don't know about other Excel versions though.

Lars-Åke

 

[Victor Delta]

I would like to create an Excel macro which will put a series of random
integers (leaving values not the formulas) in cells in a column. It should
start in the currently selected cell and fill downwards until it has
covered the number of cells given by the number in, say, cell P1. The
random integers should be between 1 and the number in cell P2 (inclusive).

Many thanks for all the helpful suggestions - I'll give them all a try.

Mike H - many thanks for your second solution although actually my limit
will be less than the range of cells so some random numbers will repeat and
not be unique.

By the way, should have told you that I'm actually using Excel 2003 - not
sure I can see the RANDBETWEEN function?

Thanks again,

V

 

[Victor Delta]

Many thanks for all the helpful suggestions - I'll give them all a try.

Mike H - many thanks for your second solution although actually my limit
will be less than the range of cells so some random numbers will repeat
and not be unique.

By the way, should have told you that I'm actually using Excel 2003 - not
sure I can see the RANDBETWEEN function?

Thanks again,

V

Mike H and Lars-Åke,

Many thanks for your macros - they both worked brilliantly and did exactly
what I asked for.

However, after running them, I realised I had come up with the wrong
solution to my problem - what I need is a random sequence not random
numbers.

The problem I am trying to address is table allocations for training events
for large numbers of people. I had assumed that a series of random numbers
say between 1 and 6 would, with a large enough number of delegates, give
almost equal numbers on each table. However, your macros showed me that it
does not. Using this method I ended up with sometimes double the number of
people on one table over another! On reflection, I guess this is bound to
happen with truly random numbers! How to learn the hard way!

So what would overcome this would be a macros which inserts say the numbers
1-6 (i.e. P2) in a random sequence and then continues with further random
sequences until it has covered P1 number of cells.

Is it possible to create a macro that will do this please?

Once again, many thanks,

V

PS This approach will also ensure that there is the widest possible mix of
people form all parts of the list on each table.

 

[Lars-Åke Aspelin]

Mike H and Lars-Åke,

Many thanks for your macros - they both worked brilliantly and did exactly
what I asked for.

However, after running them, I realised I had come up with the wrong
solution to my problem - what I need is a random sequence not random
numbers.

The problem I am trying to address is table allocations for training events
for large numbers of people. I had assumed that a series of random numbers
say between 1 and 6 would, with a large enough number of delegates, give
almost equal numbers on each table. However, your macros showed me that it
does not. Using this method I ended up with sometimes double the number of
people on one table over another! On reflection, I guess this is bound to
happen with truly random numbers! How to learn the hard way!

So what would overcome this would be a macros which inserts say the numbers
1-6 (i.e. P2) in a random sequence and then continues with further random
sequences until it has covered P1 number of cells.

Is it possible to create a macro that will do this please?

Once again, many thanks,

V

PS This approach will also ensure that there is the widest possible mix of
people form all parts of the list on each table.

Here are two more.

This shorter one distributes the table numbers making sure that there
difference in number of participants per table is never more than one.
If the number of particpants is a multiple of the number of tables,
there will be the same number of participants on each table.
However, there is no guarantee that the first P1/P2 number of
participants on the list will not end up on the same table.

Sub victor_delta2()
Dim randoms() As Double
number_of_persons = ActiveSheet.Range("P1").Value
number_of_tables = ActiveSheet.Range("P2").Value
ReDim randoms(number_of_persons)
Randomize
For i = 0 To number_of_persons - 1
randoms(i) = Rnd()
Next i
For i = 0 To number_of_persons - 1
min_rand = 1
For j = 0 To number_of_persons - 1
If randoms(j) < min_rand Then
min_rand = randoms(j)
minj = j
End If
Next j
randoms(minj) = 1
ActiveCell.Offset(minj, 0) = (i Mod number_of_tables) + 1
Next i
End Sub

This longer one does the same, with the addition that there is a
guarantee that in the first, second, third, etc sequence of P2
participants on the list, they will be distributed on all P2 tables
(which is what you asked for I guess)

Sub victor_delta3()
Dim randoms() As Double
number_of_persons = ActiveSheet.Range("P1").Value
number_of_tables = ActiveSheet.Range("P2").Value
ReDim randoms(number_of_tables)
Randomize
base = 0
While base < number_of_persons
For i = 0 To number_of_tables - 1
randoms(i) = Rnd()
Next i
For i = base To base + number_of_tables - 1
min_rand = 1
For j = 0 To number_of_tables - 1
If randoms(j) < min_rand Then
min_rand = randoms(j)
minj = j
End If
Next j
randoms(minj) = 1
If base + minj < number_of_persons Then
ActiveCell.Offset(base + minj, 0) = (i Mod number_of_tables) + 1
End If
Next i
base = base + number_of_tables
Wend
End Sub

Hope this helps / Lars-Åke

 

[Victor Delta]

Here are two more.

This shorter one distributes the table numbers making sure that there
difference in number of participants per table is never more than one.
If the number of particpants is a multiple of the number of tables,
there will be the same number of participants on each table.
However, there is no guarantee that the first P1/P2 number of
participants on the list will not end up on the same table.

Sub victor_delta2()
Dim randoms() As Double
number_of_persons = ActiveSheet.Range("P1").Value
number_of_tables = ActiveSheet.Range("P2").Value
ReDim randoms(number_of_persons)
Randomize
For i = 0 To number_of_persons - 1
randoms(i) = Rnd()
Next i
For i = 0 To number_of_persons - 1
min_rand = 1
For j = 0 To number_of_persons - 1
If randoms(j) < min_rand Then
min_rand = randoms(j)
minj = j
End If
Next j
randoms(minj) = 1
ActiveCell.Offset(minj, 0) = (i Mod number_of_tables) + 1
Next i
End Sub

This longer one does the same, with the addition that there is a
guarantee that in the first, second, third, etc sequence of P2
participants on the list, they will be distributed on all P2 tables
(which is what you asked for I guess)

Sub victor_delta3()
Dim randoms() As Double
number_of_persons = ActiveSheet.Range("P1").Value
number_of_tables = ActiveSheet.Range("P2").Value
ReDim randoms(number_of_tables)
Randomize
base = 0
While base < number_of_persons
For i = 0 To number_of_tables - 1
randoms(i) = Rnd()
Next i
For i = base To base + number_of_tables - 1
min_rand = 1
For j = 0 To number_of_tables - 1
If randoms(j) < min_rand Then
min_rand = randoms(j)
minj = j
End If
Next j
randoms(minj) = 1
If base + minj < number_of_persons Then
ActiveCell.Offset(base + minj, 0) = (i Mod number_of_tables) + 1
End If
Next i
base = base + number_of_tables
Wend
End Sub

Hope this helps / Lars-Åke

Lars-Åke

Many thanks - you are an absolute star! Both macros work very well but, as
you said, the second now does exactly what I was looking for. Problem
solved.

Thanks again,

V