event change sub

[LFKim2018]

pls help in the code:
to detect a duplicate entry on column C only
many many thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then  
         ** when an entry to column C of a duplicate (using countif(c:c,c&i)>1 formula?)
        MsgBox "No Duplicate allowed!",vbcritical
        Exit Sub
    End If
End Sub

 

[Peter_SSs]

normally when we copy a cell - we shift to the next cell then paste it - your code took good care of that.
but sometimes - when we paste we include the original cell and paste on... the drawback I am speaking of is that your code move the original cell to the last highlighted portion.

I don't fully understand that. Can you give a small specific sample of
- what you have in what cells
- what cells you select & copy
- where you place the cursor to paste
- what the current result is
- what you want the result to be

 

[LFKim2018]

Mr. Peter_SSs
example: we 'Ctrl-C' cell C2, normally we will shift to C3 then paste 'Ctrl-V' it there - your code took care of it nicely (including DRAG).
but when we 'Ctrl-C' cell C2 then select C2:C6 and Ctrl-V > (normally we can copy paste C2 down to C6 this way) - but your code will move C2 to C6... if you test this on a sheet - you will see what I meant.
hope I made it clearer now
many thanks

 

[Peter_SSs]

example: we 'Ctrl-C' cell C2, normally we will shift to C3 then paste 'Ctrl-V' it there - your code took care of it nicely (including DRAG).
but when we 'Ctrl-C' cell C2 then select C2:C6 and Ctrl-V

If you have a column where duplicates are not allowed, even attempting either of those actions seems pretty strange to me as their sole aim is to duplicate the C2 value in the column.

 

[LFKim2018]

Mr. Peter_SSs
instead of doing the clearing of the entries made by pasting - can we just apply application.undo?
many thanks

 

[Peter_SSs]

instead of doing the clearing of the entries made by pasting - can we just apply application.undo?

That doesn't seem like a great approach to me as the majority of pasted values (depending on where they were copied from) could be legitimate entries undone due to perhaps just one entry being a duplicate.

Try this alternate approach to deleting the duplicated values only.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim sClr As String
  
  Set Changed = Intersect(Target, Columns("C"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For Each c In Changed
      If Not IsEmpty(c.Value) Then
        If Intersect(ActiveSheet.UsedRange, Columns("C")).Find(What:=c.Value, After:=c, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row < c.Row Then
          sClr = sClr & ", " & c.Address(0, 0) & " (" & c.Value & ")"
          c.ClearContents
        End If
      End If
    Next c
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    If Len(sClr) Then MsgBox "Duplicales not allowed. The following cells (values) have been cleared:" & vbLf & Mid(sClr, 3)
  End If
End Sub

 

[LFKim2018]

Mr. Peter_SSs
that's just perfect.
you are indeed, the man!
many many many thanks

 

[LFKim2018]

If Len(sClr) Then MsgBox "Duplicates not allowed. The following cells (values) have been cleared:" & vbLf & Mid(sClr, 3)

a very very miniscule error to a grand opus of a work!
to err is human..