...

MS Excel VBA

Show Warning If Duplicate

Show Warning If Duplicate Value Entered In Column Cells (2 Easy Way)

Show Warning If Duplicate Value Entered In Column Cells (2 Easy Way)

Form Sources Code

  'For more: https://msexcelvba.com

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Lng As Long, Final, STR As String
    Dim Rng As Range
    
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    
    Lng = Cells(Rows.Count, "A").End(xlUp).Row
   On Error Resume Next
    If WorksheetFunction.CountIf(Range("A2:A" & Lng), Target) < 1 Then
    Exit Sub
    End If
    If WorksheetFunction.CountIf(Range("A2:A" & Lng), Target) > 1 Then
        
    STR = Empty
    Set Rng = Range("A2:A" & Lng).Find(Target, , xlValues, xlWhole)
    If Not Rng Is Nothing Then
     adres = Rng.Address
       Do
     STR = STR & Rng.Row & "      -      " & Cells(Rng.Row, "A") & Chr(10)
    Set Rng = Range("A2:A" & Lng).FindNext(Rng)
    Loop While Not Rng Is Nothing And Rng.Address <> adres
    End If
    Final = MsgBox("Row No:        Records :" & vbCrLf & Chr(10) & STR & vbLf & "Do you want to enter Dupplicated?", vbYesNo, "")
    If Final = vbYes Then MsgBox "Recording has been completed.", vbInformation, "Info"
    If Final = vbNo Then Target.ClearContents
    End If
End Sub

  

Code Explanation

      
    Dim Lng As Long, Final, STR As String
    Dim Rng As Range

  
Defined variable
   If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub  
Set Target Range Entire Column
      Lng = Cells(Rows.Count, "A").End(xlUp).Row  
Set The Last Row
  On Error Resume Next  
Skip Error Go To next
   If WorksheetFunction.CountIf(Range("A2:A" & Lng), Target) < 1 Then
    Exit Sub
    End If  
IF condition Count Less Then 1
  If WorksheetFunction.CountIf(Range("A2:A" & Lng), Target) > 1 Then
STR = Empty  
IF condition Count Greater Then 1 , STR variable Empty
  Set Rng = Range("A2:A" & Lng).Find(Target, , xlValues, xlWhole)
    If Not Rng Is Nothing Then
     adres = Rng.Address
       Do  
Find Value In Whole Range , If Nothing adres = range address
     STR = STR & Rng.Row & "      -      " & Cells(Rng.Row, "A") & Chr(10)
    Set Rng = Range("A2:A" & Lng).FindNext(Rng)
    Loop While Not Rng Is Nothing And Rng.Address <> adres
    End If  
Loop
   Final = MsgBox("Row No:        Records :" & vbCrLf & Chr(10) & STR & vbLf & "Do you want to enter Dupplicated?", vbYesNo, "")  
Msgbox VBA Yes No Prompt
  If Final = vbYes Then MsgBox "Recording has been completed.", vbInformation, "Info"
    If Final = vbNo Then Target.ClearContents
    End If  
If VBA Box Yes Then run First Line else cell clear.

 the second solution, we used Excel COUNT IF formula to warn for duplicate value entering. excel countif formula


↪️First of all, on the Warm sheet, we selected column C.
 We have selected Custom item in Allow section of Data Validation Settings tab from “Data” top menu. In the Formula section, we entered this formula:

=COUNTIF(C:C,C7)=1

       

 

error

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top