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.