Re: Random number macro

"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
> On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
> wrote:
>
>>
>>"Victor Delta" <none@nospam.com> wrote in message
>>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
>>> "Victor Delta" <none@nospam.com> wrote in message
>>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
>>>>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
=====================================

The last macro above (Sub victor_delta3()) achieved exactly what I wanted.

However, for various reasons, I need to make a couple of alterations and 
wondered if anyone could help please?

Firstly I would like the random number generator to start in a particular 
cell (say D5) rather than the selected cell.

Secondly, the column to the left (col C) has some rows marked with an X in 
the appropriate cell. Would it be possible for the macro to only enter its 
random numbers in the rows that have an X in Col C please?

TIA

V 

0
Victor
11/24/2009 8:58:07 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
668 Views

Similar Articles

[PageSpeed] 57

The RANDBETWEEN() function is included in the Analysis Toolpak.  It's an 
add-in.  To gain access to it:  Tools --> Add-Ins and tick the 'Analysis 
Toolpak' option.  Might as well tick the 'Analysis Toolpak - VBA' option 
also, as some of us around here use some of those features also in our coding.


"Victor Delta" wrote:

> "Lars-Åke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
> news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
> > On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
> > wrote:
> >
> >>
> >>"Victor Delta" <none@nospam.com> wrote in message
> >>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
> >>> "Victor Delta" <none@nospam.com> wrote in message
> >>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
> >>>>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
> =====================================
> 
> The last macro above (Sub victor_delta3()) achieved exactly what I wanted.
> 
> However, for various reasons, I need to make a couple of alterations and 
> wondered if anyone could help please?
> 
> Firstly I would like the random number generator to start in a particular 
> cell (say D5) rather than the selected cell.
> 
> Secondly, the column to the left (col C) has some rows marked with an X in 
> the appropriate cell. Would it be possible for the macro to only enter its 
> random numbers in the rows that have an X in Col C please?
> 
> TIA
> 
> V 
> 
> .
> 
0
Utf
11/24/2009 10:53:02 PM
Please see question in bottom post...

V

"JLatham" <JLatham@discussions.microsoft.com> wrote in message 
news:F179849F-8D4D-474A-850C-DC7A37406FFE@microsoft.com...
> The RANDBETWEEN() function is included in the Analysis Toolpak.  It's an
> add-in.  To gain access to it:  Tools --> Add-Ins and tick the 'Analysis
> Toolpak' option.  Might as well tick the 'Analysis Toolpak - VBA' option
> also, as some of us around here use some of those features also in our 
> coding.
>
>
> "Victor Delta" wrote:
>
>> "Lars-Åke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
>> news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
>> > On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
>> > wrote:
>> >
>> >>
>> >>"Victor Delta" <none@nospam.com> wrote in message
>> >>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
>> >>> "Victor Delta" <none@nospam.com> wrote in message
>> >>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
>> >>>>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
>> =====================================
>>
>> The last macro above (Sub victor_delta3()) achieved exactly what I 
>> wanted.
>>
>> However, for various reasons, I need to make a couple of alterations and
>> wondered if anyone could help please?
>>
>> Firstly I would like the random number generator to start in a particular
>> cell (say D5) rather than the selected cell.
>>
>> Secondly, the column to the left (col C) has some rows marked with an X 
>> in
>> the appropriate cell. Would it be possible for the macro to only enter 
>> its
>> random numbers in the rows that have an X in Col C please?
>>
>> TIA
>>
>> V
>>
>> .
>> 

0
Victor
11/24/2009 11:18:20 PM
I haven't abandonded you ... let me look all of this over between bites of 
turkey and I'll see what I can do with it in the next short while.  Lots to 
digest with all the >> >>> >>> >>>>>> in the message.


"Victor Delta" wrote:

> Please see question in bottom post...
> 
> V
> 
> "JLatham" <JLatham@discussions.microsoft.com> wrote in message 
> news:F179849F-8D4D-474A-850C-DC7A37406FFE@microsoft.com...
> > The RANDBETWEEN() function is included in the Analysis Toolpak.  It's an
> > add-in.  To gain access to it:  Tools --> Add-Ins and tick the 'Analysis
> > Toolpak' option.  Might as well tick the 'Analysis Toolpak - VBA' option
> > also, as some of us around here use some of those features also in our 
> > coding.
> >
> >
> > "Victor Delta" wrote:
> >
> >> "Lars-Åke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
> >> news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
> >> > On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
> >> > wrote:
> >> >
> >> >>
> >> >>"Victor Delta" <none@nospam.com> wrote in message
> >> >>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
> >> >>> "Victor Delta" <none@nospam.com> wrote in message
> >> >>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
> >> >>>>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
> >> =====================================
> >>
> >> The last macro above (Sub victor_delta3()) achieved exactly what I 
> >> wanted.
> >>
> >> However, for various reasons, I need to make a couple of alterations and
> >> wondered if anyone could help please?
> >>
> >> Firstly I would like the random number generator to start in a particular
> >> cell (say D5) rather than the selected cell.
> >>
> >> Secondly, the column to the left (col C) has some rows marked with an X 
> >> in
> >> the appropriate cell. Would it be possible for the macro to only enter 
> >> its
> >> random numbers in the rows that have an X in Col C please?
> >>
> >> TIA
> >>
> >> V
> >>
> >> .
> >> 
> 
> .
> 
0
Utf
11/25/2009 9:38:01 PM
Thanks. Sorry about all the >>>>>s.

V

"JLatham" <JLatham@discussions.microsoft.com> wrote in message 
news:81B57038-B9EF-4A03-87E1-B773EFF99DF3@microsoft.com...
>I haven't abandonded you ... let me look all of this over between bites of
> turkey and I'll see what I can do with it in the next short while.  Lots 
> to
> digest with all the >> >>> >>> >>>>>> in the message.
>
>
> "Victor Delta" wrote:
>
>> Please see question in bottom post...
>>
>> V
>>
>> "JLatham" <JLatham@discussions.microsoft.com> wrote in message
>> news:F179849F-8D4D-474A-850C-DC7A37406FFE@microsoft.com...
>> > The RANDBETWEEN() function is included in the Analysis Toolpak.  It's 
>> > an
>> > add-in.  To gain access to it:  Tools --> Add-Ins and tick the 
>> > 'Analysis
>> > Toolpak' option.  Might as well tick the 'Analysis Toolpak - VBA' 
>> > option
>> > also, as some of us around here use some of those features also in our
>> > coding.
>> >
>> >
>> > "Victor Delta" wrote:
>> >
>> >> "Lars-Åke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
>> >> news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
>> >> > On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
>> >> > wrote:
>> >> >
>> >> >>
>> >> >>"Victor Delta" <none@nospam.com> wrote in message
>> >> >>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
>> >> >>> "Victor Delta" <none@nospam.com> wrote in message
>> >> >>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
>> >> >>>>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
>> >> =====================================
>> >>
>> >> The last macro above (Sub victor_delta3()) achieved exactly what I
>> >> wanted.
>> >>
>> >> However, for various reasons, I need to make a couple of alterations 
>> >> and
>> >> wondered if anyone could help please?
>> >>
>> >> Firstly I would like the random number generator to start in a 
>> >> particular
>> >> cell (say D5) rather than the selected cell.
>> >>
>> >> Secondly, the column to the left (col C) has some rows marked with an 
>> >> X
>> >> in
>> >> the appropriate cell. Would it be possible for the macro to only enter
>> >> its
>> >> random numbers in the rows that have an X in Col C please?
>> >>
>> >> TIA
>> >>
>> >> V
>> >>
>> >> .
>> >>
>>
>> .
>> 

0
Victor
11/25/2009 10:59:07 PM
On Tue, 24 Nov 2009 20:58:07 -0000, "Victor Delta" <none@nospam.com>
wrote:

>"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
>news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
>> On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
>> wrote:
>>
>>>
>>>"Victor Delta" <none@nospam.com> wrote in message
>>>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
>>>> "Victor Delta" <none@nospam.com> wrote in message
>>>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
>>>>>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
>=====================================
>
>The last macro above (Sub victor_delta3()) achieved exactly what I wanted.
>
>However, for various reasons, I need to make a couple of alterations and 
>wondered if anyone could help please?
>
>Firstly I would like the random number generator to start in a particular 
>cell (say D5) rather than the selected cell.
>
>Secondly, the column to the left (col C) has some rows marked with an X in 
>the appropriate cell. Would it be possible for the macro to only enter its 
>random numbers in the rows that have an X in Col C please?
>
>TIA
>
>V 

Here is a new version of the macro that will take care of the first
alteration and, maybe, the second alteration.
You have to state cleary what your purpose with the "X"es are.
Is it just to "surpress" the display in column D? All P1 number will
then NOT be displayed. 
This is what the macro victor_delta4() does.
Or is it to "move" the display by inserting blank cells where you
don't have an "X" but still have all the P1 numbers displayed?
This is not done by macro victor_delta4().

Sub victor_delta4()
  Set start_cell = ActiveSheet.Range("D5")
  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
        If start_cell.Offset(base + minj, -1).Value = "X" Then
          start_cell.Offset(base + minj, 0) = (i Mod number_of_tables)
+ 1
        End If
      End If
    Next i
    base = base + number_of_tables
  Wend
End Sub

Hope this helps / Lars-�ke

0
Lars
11/26/2009 5:20:24 PM
"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message 
news:ardtg5ls8ai90u7svoaqfg7lkce62kgaot@4ax.com...
> On Tue, 24 Nov 2009 20:58:07 -0000, "Victor Delta" <none@nospam.com>
> wrote:
>
>>"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
>>news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
>>> On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
>>> wrote:
>>>
>>>>
>>>>"Victor Delta" <none@nospam.com> wrote in message
>>>>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
>>>>> "Victor Delta" <none@nospam.com> wrote in message
>>>>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
>>>>>>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
>>=====================================
>>
>>The last macro above (Sub victor_delta3()) achieved exactly what I wanted.
>>
>>However, for various reasons, I need to make a couple of alterations and
>>wondered if anyone could help please?
>>
>>Firstly I would like the random number generator to start in a particular
>>cell (say D5) rather than the selected cell.
>>
>>Secondly, the column to the left (col C) has some rows marked with an X in
>>the appropriate cell. Would it be possible for the macro to only enter its
>>random numbers in the rows that have an X in Col C please?
>>
>>TIA
>>
>>V
>
> Here is a new version of the macro that will take care of the first
> alteration and, maybe, the second alteration.
> You have to state cleary what your purpose with the "X"es are.
> Is it just to "surpress" the display in column D? All P1 number will
> then NOT be displayed.
> This is what the macro victor_delta4() does.
> Or is it to "move" the display by inserting blank cells where you
> don't have an "X" but still have all the P1 numbers displayed?
> This is not done by macro victor_delta4().
>
> Sub victor_delta4()
>  Set start_cell = ActiveSheet.Range("D5")
>  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
>        If start_cell.Offset(base + minj, -1).Value = "X" Then
>          start_cell.Offset(base + minj, 0) = (i Mod number_of_tables)
> + 1
>        End If
>      End If
>    Next i
>    base = base + number_of_tables
>  Wend
> End Sub
>
> Hope this helps / Lars-�ke

Lars-�ke

Very many thanks for the new macro. I'll give it a try.

I'm sorry I did not make it totally clear what effect I wanted the Xs to 
have - I was trying to keep things as brief and simple as possible, but had 
overlooked the implications of doing this. My spreadsheet contains the all 
the names of people who might attend one of my training events. In column C 
I indicate with an X those who will be attending the next one - so P1 is the 
number of Xs in col C.

What would be brilliant would be if the macro could run down column D but 
only entering it's random (table) numbers in the cells of column D where 
there is an X in col C.

I gather from what you say that the latest macro probably will not do this 
but I'll give it a try.

Thank you so very much for all your help. It is much appreciated.

V 

0
Victor
11/26/2009 8:51:34 PM
On Thu, 26 Nov 2009 20:51:34 -0000, "Victor Delta" <none@nospam.com>
wrote:

>"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message 
>news:ardtg5ls8ai90u7svoaqfg7lkce62kgaot@4ax.com...
>> On Tue, 24 Nov 2009 20:58:07 -0000, "Victor Delta" <none@nospam.com>
>> wrote:
>>
>>>"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
>>>news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
>>>> On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
>>>> wrote:
>>>>
>>>>>
>>>>>"Victor Delta" <none@nospam.com> wrote in message
>>>>>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
>>>>>> "Victor Delta" <none@nospam.com> wrote in message
>>>>>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
>>>>>>>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
>>>=====================================
>>>
>>>The last macro above (Sub victor_delta3()) achieved exactly what I wanted.
>>>
>>>However, for various reasons, I need to make a couple of alterations and
>>>wondered if anyone could help please?
>>>
>>>Firstly I would like the random number generator to start in a particular
>>>cell (say D5) rather than the selected cell.
>>>
>>>Secondly, the column to the left (col C) has some rows marked with an X in
>>>the appropriate cell. Would it be possible for the macro to only enter its
>>>random numbers in the rows that have an X in Col C please?
>>>
>>>TIA
>>>
>>>V
>>
>> Here is a new version of the macro that will take care of the first
>> alteration and, maybe, the second alteration.
>> You have to state cleary what your purpose with the "X"es are.
>> Is it just to "surpress" the display in column D? All P1 number will
>> then NOT be displayed.
>> This is what the macro victor_delta4() does.
>> Or is it to "move" the display by inserting blank cells where you
>> don't have an "X" but still have all the P1 numbers displayed?
>> This is not done by macro victor_delta4().
>>
>> Sub victor_delta4()
>>  Set start_cell = ActiveSheet.Range("D5")
>>  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
>>        If start_cell.Offset(base + minj, -1).Value = "X" Then
>>          start_cell.Offset(base + minj, 0) = (i Mod number_of_tables)
>> + 1
>>        End If
>>      End If
>>    Next i
>>    base = base + number_of_tables
>>  Wend
>> End Sub
>>
>> Hope this helps / Lars-�ke
>
>Lars-�ke
>
>Very many thanks for the new macro. I'll give it a try.
>
>I'm sorry I did not make it totally clear what effect I wanted the Xs to 
>have - I was trying to keep things as brief and simple as possible, but had 
>overlooked the implications of doing this. My spreadsheet contains the all 
>the names of people who might attend one of my training events. In column C 
>I indicate with an X those who will be attending the next one - so P1 is the 
>number of Xs in col C.
>
>What would be brilliant would be if the macro could run down column D but 
>only entering it's random (table) numbers in the cells of column D where 
>there is an X in col C.
>
>I gather from what you say that the latest macro probably will not do this 
>but I'll give it a try.
>
>Thank you so very much for all your help. It is much appreciated.
>
>V 

Give this macro a try then;

Sub victor_delta5()
 Set next_cell = ActiveSheet.Range("D5")
 Dim randoms() As Double
 Dim results() As Integer
  number_of_persons = ActiveSheet.Range("P1").Value
 ReDim results(number_of_persons)
 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
        results(base + minj) = (i Mod number_of_tables) + 1
      End If
    Next i
    base = base + number_of_tables
  Wend
  numbers_left = number_of_persons - 1
  While numbers_left >= 0 And next_cell.Row < 1000
    If next_cell.Offset(0, -1).Value = "X" Then
      next_cell.Value = results(numbers_left)
      numbers_left = numbers_left - 1
    End If
    Set next_cell = next_cell.Offset(1, 0)
  Wend
End Sub

The "And next_cell.Row < 1000" should not be need. I just put it there
to avoid a lengthy loop endiing with a crash in the case that the
number of "X" in the C column (from row 5 and donw)  is less than the
value in P1.

Hope this helps / Lars-�ke
0
Lars
11/27/2009 12:29:48 AM
"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message 
news:v57ug51ki2p7fr3l0p0kjls49gj3p7aoa6@4ax.com...
> On Thu, 26 Nov 2009 20:51:34 -0000, "Victor Delta" <none@nospam.com>
> wrote:
>
>>"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
>>news:ardtg5ls8ai90u7svoaqfg7lkce62kgaot@4ax.com...
>>> On Tue, 24 Nov 2009 20:58:07 -0000, "Victor Delta" <none@nospam.com>
>>> wrote:
>>>
>>>>"Lars-�ke Aspelin" <larske@REMOOOVE.telia.com> wrote in message
>>>>news:7hh155d5tolk3knj51bbjg60vl7l32bnri@4ax.com...
>>>>> On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <none@nospam.com>
>>>>> wrote:
>>>>>
>>>>>>
>>>>>>"Victor Delta" <none@nospam.com> wrote in message
>>>>>>news:eprLDCD$JHA.1380@TK2MSFTNGP02.phx.gbl...
>>>>>>> "Victor Delta" <none@nospam.com> wrote in message
>>>>>>> news:uvYrvrB$JHA.200@TK2MSFTNGP05.phx.gbl...
>>>>>>>>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
>>>>=====================================
>>>>
>>>>The last macro above (Sub victor_delta3()) achieved exactly what I 
>>>>wanted.
>>>>
>>>>However, for various reasons, I need to make a couple of alterations and
>>>>wondered if anyone could help please?
>>>>
>>>>Firstly I would like the random number generator to start in a 
>>>>particular
>>>>cell (say D5) rather than the selected cell.
>>>>
>>>>Secondly, the column to the left (col C) has some rows marked with an X 
>>>>in
>>>>the appropriate cell. Would it be possible for the macro to only enter 
>>>>its
>>>>random numbers in the rows that have an X in Col C please?
>>>>
>>>>TIA
>>>>
>>>>V
>>>
>>> Here is a new version of the macro that will take care of the first
>>> alteration and, maybe, the second alteration.
>>> You have to state cleary what your purpose with the "X"es are.
>>> Is it just to "surpress" the display in column D? All P1 number will
>>> then NOT be displayed.
>>> This is what the macro victor_delta4() does.
>>> Or is it to "move" the display by inserting blank cells where you
>>> don't have an "X" but still have all the P1 numbers displayed?
>>> This is not done by macro victor_delta4().
>>>
>>> Sub victor_delta4()
>>>  Set start_cell = ActiveSheet.Range("D5")
>>>  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
>>>        If start_cell.Offset(base + minj, -1).Value = "X" Then
>>>          start_cell.Offset(base + minj, 0) = (i Mod number_of_tables)
>>> + 1
>>>        End If
>>>      End If
>>>    Next i
>>>    base = base + number_of_tables
>>>  Wend
>>> End Sub
>>>
>>> Hope this helps / Lars-�ke
>>
>>Lars-�ke
>>
>>Very many thanks for the new macro. I'll give it a try.
>>
>>I'm sorry I did not make it totally clear what effect I wanted the Xs to
>>have - I was trying to keep things as brief and simple as possible, but 
>>had
>>overlooked the implications of doing this. My spreadsheet contains the all
>>the names of people who might attend one of my training events. In column 
>>C
>>I indicate with an X those who will be attending the next one - so P1 is 
>>the
>>number of Xs in col C.
>>
>>What would be brilliant would be if the macro could run down column D but
>>only entering it's random (table) numbers in the cells of column D where
>>there is an X in col C.
>>
>>I gather from what you say that the latest macro probably will not do this
>>but I'll give it a try.
>>
>>Thank you so very much for all your help. It is much appreciated.
>>
>>V
>
> Give this macro a try then;
>
> Sub victor_delta5()
> Set next_cell = ActiveSheet.Range("D5")
> Dim randoms() As Double
> Dim results() As Integer
>  number_of_persons = ActiveSheet.Range("P1").Value
> ReDim results(number_of_persons)
> 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
>        results(base + minj) = (i Mod number_of_tables) + 1
>      End If
>    Next i
>    base = base + number_of_tables
>  Wend
>  numbers_left = number_of_persons - 1
>  While numbers_left >= 0 And next_cell.Row < 1000
>    If next_cell.Offset(0, -1).Value = "X" Then
>      next_cell.Value = results(numbers_left)
>      numbers_left = numbers_left - 1
>    End If
>    Set next_cell = next_cell.Offset(1, 0)
>  Wend
> End Sub
>
> The "And next_cell.Row < 1000" should not be need. I just put it there
> to avoid a lengthy loop endiing with a crash in the case that the
> number of "X" in the C column (from row 5 and donw)  is less than the
> value in P1.
>
> Hope this helps / Lars-�ke

Lars-�ke

That's absolutely fantastic! The macro works brilliantly - does just what I 
wanted.

Thank you so much.

V 

0
Victor
11/27/2009 3:11:18 PM
Reply:

Similar Artilces:

Converting user-name to 'random' number
Hi guys, I wondered if you could help out a friend of mine - I'm just passing on his message: -------------------------- I'm trying to automate some coursework tasks for the new course and would like to randomise the data sets used by each student. Ideally I'd like them to type their name into a pre-set cell, and then for this to be converted to a long-ish number. I can then use the number to add different deliberate errors to the data. I don't want to use RANNOR (random number generator) as this would make marking a nightmare. Does anyone know how to convert a cell cont...

Question re: Outlook find feature
I have had a number of users observe that when they use the Find feature in Outlook, it pulls older inbox items first, and newer inbox items later. They have questioned me as to wether this behavior can be modified. I have not found any authoritative answers to this question. My guess is that Outlook queries some index first, so indexed items show up before any newer, non-indexed items... Any documentation on this? Thanks... you are correct and there is nothing you can do to change the behavior. I don't believe there is any documentation on it, short of what we've dis...

Re: Jpg files and E-mail issue.
Please help, If I am in the wrong place please exuse me... I just upgraded from Outlook express to Outlook 2003. My issue is when I recieve an e-mail with attached jpg pictures, how do I automatically view them each and every time? Keep in mind there are no boxes with red x's in the e-mail either. outlook doesn't support inline images. see http://www.slipstick.com/mail1/inline.htm for options -- "Joseph Thompson" <Joseph Thompson@discussions.microsoft.com> wrote in message news:1E9FAF22-D5CA-40A3-889A-5C9ECF8C56DA@microsoft.com... > Please help, If I am i...

Add hyphens to a number
In one column, each cell has a 16 digit number. I need to put hyphens between 4 different numbers in each cell. Each cell/number will have the hyphen in the same place. I was successful doing this once I believe through Format\Cells\Number\Custom but I did not write down the steps how to do it. Any clues? Thanks texasphil -- texasphil ------------------------------------------------------------------------ texasphil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32615 View this thread: http://www.excelforum.com/showthread.php?threadid=541305 You didn&#...

Couple of queries re macros (end of data / delete row if...)
Hi, firstly when using the basic record a macro feature, I often want to select the entire range for sorting / formating etc. The problem is if any of the fields are blank, you can end up pressing end | down several times, and this is no good for changing data. So question is, is there a way of doing this via the record a macro screen, or is there some code I can paste into my existing macro? Secondly, how do I get a macro to check the contents of column N, and if it contains the words "Loaded at Site", delete the entire row and resort the spreadsheet. Thanks in advance. You co...

Error Number 0x800ccc0E
In the support base it says that this happens when I am connected to MSN and using a non MSN address. Unfortunately Im not (I don't think) using MSN to access the web. HELP!! Thanks Matt Hi! This code (see Q208814): 0x800CCC0E FAILED_TO_CONNECT Cannot connect to server. can mean the following: your ISP server is exist, but no service exists at specified port (e.g. you have specified server.myisp.com and port 26, but the service is located at port 25). Alexander Gorlach, MAPILab Ltd. -- Must have Outlook add-ins http://www.mapilab.com/ > -----Original Message----- > F...

Filtering Numbers
This is probably a very easy question to answer but I am having great difficulty. I simply want to filter a lot of data by numbers. For example I have a lot of pressure data with readings every second. I only want to use every hundredth point. If I try autofilter - custom- ends with- it doesn't work. Only works with text. None of the other number filters are of use. Is it possible to filter by row number. For instance to display every hundredth row? Any help would be appreciated. One way, in a helper column at the end of your data, put the following formula and then copy it down a...

1. Selecting field with "enter"; 2. permanently accepting macros
I have recently upgraded to Access 2007 and two characteristics of my former database are not coming through & I want to get them back. First, in Access 2003, I had set it up so that if I hit "enter" the entire field would be selected. Now "enter" causes the cursor to jump to the next field. How can I change this? Secondly, In my database forms, I had incorporated buttons. Now, each time I open the database, I am given a warning about macros and I have to manually tell the program that the macros are trusted. Unfortunately, Access doesn't accept this an...

Incidents/Cases ticket number
Hi all, How can I change the generator for automatic cases ticket number ? By default appears CAS-XXXX, how can i change the prefix and reset the numerator ? Thanks, Hugo ...

Maintain PO Line item number sequence when one is deleted.
Once a PO is printed (released), if a line item is deleted, all subsequent line item numbers are resequenced. I would prefer they are not resequenced to maintain accurate history and to all me to tell the vendor that line item 13 was deleted. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

Random automatic reply
Outlook is sending a blank reply to nearly every message I receive. The time on the sent message is usually the exact same time as the message to which it replied. There is nothing attached, no subject line and nothing in the body. I've scanned numerous times for viruses with no success. Any ideas. Amos ...

Backspace randomly starts deleting bullets...
I write a lot of lecture outlines in MS Word using bullets. It works extremely well, however, the funniest thing happens and I'd love to be able to stop it. Everything will work great for a time: When I press tab, the bullet will indent one tab forward. When I press backspace, the bullet will de-indent one tab backward. .... then, all of the sudden, without any purposeful doing on my part: When I press tab, the bullet will indent one tab forward. However, when I press backspace, it deletes the bullet. .... and this continues for the rest of the document and I can't...

Contact Auto Number
Hello, Does anyone have any clever ideas about how to configure a unique id field on the contact entity? What ive done so far is to add a new button via the isv.config which points to an asp page, which subsequently calls a stored procedure on a seperate database table(increments by 1 etc.,)and puts the value in the crm form. However, i am relying on the users to click on the button, ideally i would like it to populate the membership number field when the screen is displayed (and is blank). Anyone got any clever ideas? Jo H ...

Excel should have a second 'Freeze Panes' option that works in re.
Excel should have a second 'Freeze Panes' option that works in reverse for the bottom of the screen to the existing 'Freeze Panes' option that works for the top of the screen. This the user can have column footers and row summaries at the right of the screen at the same time as having column headers and row descriptions at the left. When faced with this issue, I have had to expand my "freeze panes" at the top and place info on top of the column headings. In my case the data I would have liked to be on the bottom was some totals and percentages. When placed ...

Macro Running Slow
I have used the "Record a New Macro" tool often with great luck. Today (with brand new office 2003 version) I recorded a macro to insert a row below the selected row, then merge merge and format left (2 steps) cells atop each other (A10 with A11 then B10 with B11 and so on). The merging and formatting only takes place in 13 Columns (A through M). The process however takes about 5 or 6 full seconds, which takes a lot of time when I need to run it 500 times or so. I have done a lot of much more complex macros and they always execute almost instantly, but I can sit and watch this on...

Re-sizing Excel Help Screen
I did something that causes my excel help function to occupy 1/2 of the screen from top to bottom and I don't know how to get it back to its usual size. I can change the width of the screen but I can't seem to change the height. There does not appear to be any handles showing up at the top or bottom or even corner to allow me to bring the screen back down to a smaller size heightwise. Can anybody help? Dave, Not absolutely sure, but if it is Excel 2000, it might be this problem that Chip Pearson helped me with. I re-print Chip's response You can fix this "feature&q...

Re: "SUMIF" or "COUNTIF" to count alpha characters as .5 ?
small example, we need to count employees for SICK DAY ( S ) as 1 point, and a late or TARDY ( T ) as .5 point. A1 = S B1 = T it should total to 1.5 I tried to use: =COUNTIF(A1:B1, "S" +1) ( equals 1 ) and I tried: =COUNTIF(A1:B1, "T" + .5 ) ( equals .5 ) But neither worked.....I also tried the SUMIF but no luck..... Any Ideas??? Thanks so much, this is a great group! You can use the following formula to total the S and T values: =COUNTIF(A1:B1,"S")+COUNTIF(A1:B1,"T")*0.5 Ange...

Mailbox not re-enabled after increasing mailbox storage limits.
There is a mailbox which exceeded its sorage limits last week. By mistake on Friday last I set the limits to around 200 KB instead of 200MB On discovering the mistake today, I have now set the mailbox limits to much bigger figures around 600 Mb upwards, but nothing I can do seems to make Exchange pay any attention to the new figures. It is still rejecting emails to the account despite having manually run Mailbox Cleanup agent and Mailbox management process. I even tried dismount and mount of the mailbox store. Why why why, are these new limits not having any effect? What do I need to d...

I need help on a Macro
hi guys i really need to know how if i enter into cell F12 35 how can i get a message box to appear with say "You need to restock" written on it and the to buttons Yes and No? Can anybody help me???????????????????????? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = "$F$12" And Target.Value = 35 Then MsgBox "You need to restock", vbYesNo End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs...

Disable macro selections under tools menu
I have macros to automate on a sheet and want to disable the option to run a macro under the tool selection on the menu bar. How would I do this? Thank you Change the macro's declaration from public to private. If you want to hide all of the procedures in a specific module then use Option Private Module at the top of the code module. -- HTH... Jim Thomlinson "Jeremy" wrote: > I have macros to automate on a sheet and want to disable the option to run a > macro under the tool selection on the menu bar. How would I do this? > > &...

Safepay
A major bank in Canada (TD Canada Trust) has a requirement for an auto-incrementing file number in the header and there is no way to do this with the existing fields in safepay. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions...

Excel with Macros
I have protected a worksheet with macros and when I try an run them I get "run time error 1004" I need to able to protect the sheet from alterations by others. What is the error and how do I fix it? regds Hi depending on the macro actions you may have to unprotect the sheet at the beginning of your macro and protect it again at the end of the run -- Regards Frank Kabel Frankfurt, Germany Rajesh wrote: > I have protected a worksheet with macros and when I try an > run them I get "run time error 1004" I need to able to > protect the sheet from alterations by ...

how to check if CEdit input is a number or not?
I want to know what is the easy way to check if a string is a number or not? the number can be int, float, double, scientific,... what is the easy way for only interger? Look at this: http://www.codeproject.com/editctrl/validatingedit.asp --------- Ajay Kalra ajaykalra@yahoo.com You may find this derived control useful. http://www.codeguru.com/Cpp/controls/editctrl/maskededitcontrols/article.php/c3915/ Tom "kathy" <yqin_99@yahoo.com> wrote in message news:1141853748.415449.24570@i40g2000cwc.googlegroups.com... >I want to know what is the easy way to check if a str...

Changing office v.X serial number
Hi, I have a copy of Office v.X for Mac and had it installed on my computer. I recently purchased a new iMac and wanted to install this software onto the second Mac, but because they are connected to the same network then it comes up with the error that the serial number is already in use on the network, and shuts down. So, i have purchased another license to try and install with. So, i had deleted the install of Office and tried to reinstall with the new number. The problem is it didnt ask for the new number, it took the old one again. Now, im not so much a Macintosh person, i have us...

number
the error is :"the number in this cell is formatted as text or preceded by an astrophe", It means we must convert it to number and we can do, but I don't want this error appear in other excel file again.How can I do? This can be easily avoided. Before making any entries into a worksheet or template, make sure the cells to be used for numerical input have been pre-formatted to General, not Text. -- Gary''s Student - gsnu201001 "hungayun" wrote: > the error is :"the number in this cell is formatted as text or preceded by an > astrop...