Dear experts,

I've a table. Column A is for account numbers. I put a formula in
"Validation" to prevent duplicate records. It works well whenever I input
numbers. However, it doesn't work when I pasted a duplicate numbers from
another workbook. In this regard, please advise how to fix this problem so
that column A will also reject duplicate numbers from paste function.

Thanks in advance.

it doesn't work when I pasted a duplicate numbers from another workbook.

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, try Vasant's code
below, which will prevent duplicate entries -- including preventing
copy-paste or dragging which would defeat data validation -- for col A in a
sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right. Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Freshman" wrote:
I've a table. Column A is for account numbers. I put a formula in
"Validation" to prevent duplicate records. It works well whenever I input
numbers. However, it doesn't work when I pasted a duplicate numbers from
another workbook. In this regard, please advise how to fix this problem so
that column A will also reject duplicate numbers from paste function.

Max,

Thanks for your tips. I notice your MVP icon is changed to gold.
Congratulations! You deserve this title.

Best regards tio you and your family.

"Max" wrote:

it doesn't work when I pasted a duplicate numbers from another workbook.

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, try Vasant's code
below, which will prevent duplicate entries -- including preventing
copy-paste or dragging which would defeat data validation -- for col A in a
sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right. Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Freshman" wrote:
I've a table. Column A is for account numbers. I put a formula in
"Validation" to prevent duplicate records. It works well whenever I input
numbers. However, it doesn't work when I pasted a duplicate numbers from
another workbook. In this regard, please advise how to fix this problem so
that column A will also reject duplicate numbers from paste function.

Welcome, and thanks for the congrats. That little gold icon just means that
I've reached "gold level contributor" in this newsgroup (=500 greens marked
against my responses here). But I'm not an MVP. Real MVPs are those
responders with MVP icons (blue diamond icons, badged with the letters MVP).
Albeit the MVP badging doesn't seem to show up in this MS web interface for a
good few more real MVPs that I recognize.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Freshman" wrote:
Max,

Thanks for your tips. I notice your MVP icon is changed to gold.
Congratulations! You deserve this title.

Best regards to you and your family.

Max,

Could you be kind to explain for me what is the range used by the Find
function on this line of your code:

If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then

Thank you for sharing this useful code.

"Max" wrote:

it doesn't work when I pasted a duplicate numbers from another workbook.

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, try Vasant's code
below, which will prevent duplicate entries -- including preventing
copy-paste or dragging which would defeat data validation -- for col A in a
sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right. Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Freshman" wrote:
I've a table. Column A is for account numbers. I put a formula in
"Validation" to prevent duplicate records. It works well whenever I input
numbers. However, it doesn't work when I pasted a duplicate numbers from
another workbook. In this regard, please advise how to fix this problem so
that column A will also reject duplicate numbers from paste function.

Hi

Target in this case will be the Number you have entered.

Cells(1 ,1) is row 1 of column 1 (A1)

Cells(Intersect(Target, Columns(1)).Row - 1, 1)
will be -1 row above the row where the Target exists in column 1 (A)

so the range will be from cell A1 down to the row in column A above your new
entry.
Find operates on this range to see if the target value already exists.

The second address in the range could equally have been written as
Cells(Target.row-1, Target.column)
or, since we are only dealing with column A
Cells(Target.Row-1, 1)

--
Regards
Roger Govier

"flores" wrote in message
...
Max,

Could you be kind to explain for me what is the range used by the Find
function on this line of your code:

If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then

Thank you for sharing this useful code.

"Max" wrote:

it doesn't work when I pasted a duplicate numbers from another
workbook.

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, try Vasant's code
below, which will prevent duplicate entries -- including preventing
copy-paste or dragging which would defeat data validation -- for col A in
a
sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right.
Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Freshman" wrote:
I've a table. Column A is for account numbers. I put a formula in
"Validation" to prevent duplicate records. It works well whenever I
input
numbers. However, it doesn't work when I pasted a duplicate numbers
from
another workbook. In this regard, please advise how to fix this problem
so
that column A will also reject duplicate numbers from paste function.

Thank you for your thorough explanation.

The real task I have at hand and I hope that you can help me get a solution
faster than the one week it would take me to figure it out is explained next:

I need to have the Find function look for duplicates not only above the
Target entry. I also need it to look at the entries below the target entry
in column A. Could you provide an expression that will cause the Find
function to search for duplicates from A2 to the end of the list (€œdatabase€�
list). For example, if the database's last row is 15 and the Target entry
cell is A2, then the Find function should search for duplicates on A2:15
except A2 (skip the target entry).
A2:A15 except Target A2. I like the expression to be self extending to find
the end of the growing list.

Your help is greatly appreciated.

Flores


Roger,

Thank you for your thourough explanation.

The real task I have at hand, and I hope that you can help me get solution
faster than the one week it would take me to figure out is next:

I need to have the Find function look for duplicates not only above the
Target entry. I also need it to look at the entries below the target entry
in column A. Could you provide an expresion that will cause the Find
function to search for duplicates from A2 to the end of the list (database
list). For example, if the database's last row is 15 and the Target entry
cell is A2, then the Find function should search for duplicates on A2:15
except A2 (skip the target entry).
A2:A15 except Target A2. I hope that expresion uses self extending to find
the end of the growing list.

Your help would be greatly appreciate.

Flores



"Roger Govier" wrote:

Hi

Target in this case will be the Number you have entered.

Cells(1 ,1) is row 1 of column 1 (A1)

Cells(Intersect(Target, Columns(1)).Row - 1, 1)
will be -1 row above the row where the Target exists in column 1 (A)

so the range will be from cell A1 down to the row in column A above your new
entry.
Find operates on this range to see if the target value already exists.

The second address in the range could equally have been written as
Cells(Target.row-1, Target.column)
or, since we are only dealing with column A
Cells(Target.Row-1, 1)

--
Regards
Roger Govier

"flores" wrote in message
...
Max,

Could you be kind to explain for me what is the range used by the Find
function on this line of your code:

If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then

Thank you for sharing this useful code.

"Max" wrote:

it doesn't work when I pasted a duplicate numbers from another
workbook.

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, try Vasant's code
below, which will prevent duplicate entries -- including preventing
copy-paste or dragging which would defeat data validation -- for col A in
a
sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right.
Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Freshman" wrote:
I've a table. Column A is for account numbers. I put a formula in
"Validation" to prevent duplicate records. It works well whenever I
input
numbers. However, it doesn't work when I pasted a duplicate numbers
from
another workbook. In this regard, please advise how to fix this problem
so
that column A will also reject duplicate numbers from paste function.