Prev: Pop up message asks if I really want to edit the live office d
Next: Color change for Column Chart
From: p45cal on 5 May 2010 10:16

Bob Umlas, Excel MVP;716765 Wrote:
>
No VBA required.


I suspect it might be, Bob. How otherwise would "if a value is entered
that already exists in the range, the value that already exists would
become empty and the new location would hold the unique value" be done
without it?



B
o
b

U
m
l
a
s
,

E
x
c
e
l

M
V
P
;
7
1
6
7
6
5

W
r
o
t
e
:


>
No VBA required. Suppose it's column B which yuo want to limit to
unique
> values. Select the whole column, use Data/Validation, select Custom from
the
> "Allow" dropdown, enter this formula:
> =COUNTIF(B:B,B1)=1
> and perhaps click the Error Alert tab to supply a message when a
duplicate
> value is entered.
>
> "Michael" wrote:
>
> > I am looking for a way to limit a named range to unique values. So
> > that if a value is entered that already exists in the range the value
> > that already exists would become empty and the new location would
hold
> > the unique value.
> >
> > I got something sort of working with the selection change event but
it
> > took too long to cycle through each cell in the range to make sure it
> > did not equal the target value. was not pretty. The range is only
> > like 50 cells.
> >
> > I am looking for advise on how to make it work.
> >
> > Thanks
> > .
> >


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=200469

http://www.thecodecage.com/forumz

From: Michael on 5 May 2010 10:47
On May 4, 5:53 pm, Tom Hutchins
<TomHutch...(a)discussions.microsoft.com> wrote:
> Try this Worksheet_Change event code. I named the range which should have no
> duplicate values UniqRng in this example.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim tgt As Range, xx As Range
> For Each tgt In Target
>     If (Not Intersect(tgt, ActiveSheet.Range("UniqRng")) Is Nothing) And _
>         (Len(tgt.Value) > 0) Then
>         For Each xx In ActiveSheet.Range("UniqRng")
>             If xx.Address <> tgt.Address Then
>                 If xx.Value = tgt.Value Then
>                     xx.Value = vbNullString
>                 End If
>             End If
>         Next xx
>     End If
> Next tgt
> End Sub
>
> This code should be placed on the code page of the worksheet where you want
> this to work.
>
> Hope this helps,
>
> Hutch

This worked perfectly. Thank you very much.!
First  |  Prev  | 
Pages: 1 2
Prev: Pop up message asks if I really want to edit the live office d
Next: Color change for Column Chart