Hi David

you did the right thing by changing the Dim myvals(x) As String and by
deleting and recreating the myrng range name before running it for a
different number of cells ... but ...

the way the code works is that it checks the 20 (or whatever cells) and if
it finds a duplicate it recalcs all the cells and checks them again ... so
unfortunately it seems that it is taking a long time to generate 20 cells
without duplication.

I can't think of any way of speeding it up ... unless you only include in
the myrng range name those fields where duplications are possible, rather
than all 20 (if this is an option), then it will have less values to check
and compare.

if this doesn't work, then AFAIK the sitauation needs to be approach
differently but i'm not sure how.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" wrote in message
...
Julie,
I redefined the myrng range to the number of cells I was trying. When
I
redefined the range and changed the number I worked, but when I went to 20
cells in the myrng range and changed the number to 19 that is when it is
taking so long.

Thanks,
David L

"JulieD" wrote:

Hi David

did you delete & recreate "myrng" every time you tested a different
number
of cells?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" wrote in message
...
Hi Julie,

Thank you for your help. I had told you wrong about the number of
cells I
have to check for duplicates. Instead of 17 there are 20. I change
the
macro to read Dim myvals(19) As String. I am not sure if this is
right.
I
can change the range to have 18 cells and change the macro to Dim
myvals(17)
As String and the macro runs and works and it takes less than 30
seconds.
With the 20 cells I just had to cancel the macro after it had been
running
for 15 minutes. I was wondering if there is something else I need to
change
to make it run faster.

Thanks,
David L

"JulieD" wrote:

Hi David

okay here it is

first, select your 17 cells (use the control key to get them) and then
click
in the name box (little box to left of formula bar) and type
myrng
and press ENTER

then, right mouse click on a sheet tab and choose view code
in the VBE Window choose insert / module
on the right hand side of the screen copy & paste this
---

Sub eliminatedups()


Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer
Dim myvals(16) As String

he
Calculate
i = 0
For Each c In Range("myrng")
myvals(i) = c.Value
i = i + 1
Next

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 0 To UBound(myvals) - 1
' If the element is greater than the element
' following it, exchange the two elements.
If myvals(i) myvals(i + 1) Then
NoExchanges = False
Temp = myvals(i)
myvals(i) = myvals(i + 1)
myvals(i + 1) = Temp
ElseIf myvals(i) = myvals(i + 1) Then 'added GD
GoTo here
End If
Next i
Loop While Not (NoExchanges)

msgbox "All OK"

End Sub

---

then use ALT & F11 to switch back to your workbook and create a
button -
assign the macro to the button and click it ....it should run through
until
there are no duplicates in the 17 cells.

let me know how you go.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" wrote in message
...
Julie,
I do believe that would help. Can you give me an example of how to
do
this?
I can and the macro to a button to recaluclate the cells.
Thanks,
David

"JulieD" wrote:

Hi David

this could be difficult, as any code that i can envisage running
would
cause
the sheet to recalculate - which means that it would change all of
the
17
names not just the duplicated ones (that's if all 17 of them are
populated
by a formula in the cell) .. a solution, therefore (from my
knowledge
base
anyway) would be a macro that keeps running until all 17 locations
have
unique names in them, would that be an option .. however,
basically,
apart
from the fact you have to check all 17 manually, you could achieve
the
same
by pressing the F9 key ......... so, do you think a macro like this
would
be
useful?
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" wrote in message
...
Hi Julie,
You are right about the duplicate names. Actually, I have
several
list
to
pick from. I have a front page. It has 17 different locations I
need
names
to go into. Each list I randomly pick from has its own catogory.
The
different catogories contain some of the same names. I need to
pick
a
random
name from each catogory and not have them duplicate the same name
within
the
17 locations. If there is a vba code that can be written, I
would
appericate
the help and instructions own how to use the code within the
worksheet.

Thanks again,
David L

"JulieD" wrote:

Hi David

a formula can only affect the cell it is in, it can't move or
change
another
cell for this you need some code ...

from what i gather you've got two lists from which you're
picking
random
names, however, sometimes you get a duplicated name when you
combine
the
results of the two lists and you don't want this to happen? if
this
is
the
case, i don't understand how moving a name from g5 to j5 will
help?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" wrote in message
...
Is there a way to move a cell contents to another cell with a
formula.
ex:
if a5="Name" then move g5 to j5? Also, I am using
=INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random
names
from
a
list.
I have the formula in different place pick random names from
different
list.
This does work, but I have different list with some of the
same
names
and
with the random pick I do not want the same name to appear.
--
Thanks for any and all help.
Davidl