Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
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 "Victor Delta" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
On Fri, 3 Jul 2009 21:11:12 +0100, "Victor Delta"
wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
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 "Mike H" wrote: 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 "Victor Delta" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
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" 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
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" wrote: On Fri, 3 Jul 2009 21:11:12 +0100, "Victor Delta" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
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 On Fri, 3 Jul 2009 14:00:01 -0700, Mike H wrote: 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" wrote: On Fri, 3 Jul 2009 21:11:12 +0100, "Victor Delta" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
"Victor Delta" 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). 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
"Victor Delta" wrote in message
... "Victor Delta" 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). 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta"
wrote: "Victor Delta" wrote in message ... "Victor Delta" 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). 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. 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number macro
"Lars-Åke Aspelin" wrote in message
... On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" wrote: "Victor Delta" wrote in message ... "Victor Delta" 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). 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Addding a Random number to a fixed number..... | Excel Discussion (Misc queries) | |||
How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
Random number Macro | Excel Discussion (Misc queries) | |||
Generating (in a random order)each number once from a given number | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) |