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" <(E-Mail Removed)> wrote in message
> news:56218937-9A01-4AE3-B76A-(E-Mail Removed)...
> > 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.
> >>
Similar Threads
