Issues in Duplicate Value should not update in userform

[jawaharprm]

Hi All,

Dim lrow As Long
Dim cl As Range
Dim cel As Range

lrow = [MSPPData].Cells(Rows.Count, 1).End(xlUp).Row
    Range("N2").FormulaR1C1 = "=RC[-12]&RC[-11]"
    Range("N2:N & lrow").FillDown

The above code is Concatenate B&C columns is working fine, but it is not filldown till last row

code no 2

'finding duplicate values
    For Each cl In MSPPData.Range("N2", Range("N" & Rows.Count).End(xlUp))
        If Application.CountIf(Range("N:N"), cl.Value) > 1 Then
       
           MsgBox "Hi " & Application.UserName & " - Duplicate found, please check the list"
         
        Exit Sub
        End If
    Next cl

the above code is for find duplicate if the values are same it should not update and popup msg will display
but if i enter non duplicate & duplicate value it is updating in the next sheet and popup msg is show, can u pls help on same

Thanks
Jawahar

 

[p45cal]

Best attach a workbook where this happens.
If MSPPData is a sheet, the code
lrow = [MSPPData].Cells(Rows.Count, 1).End(xlUp).Row
finds the last row in column A; maybe column A doesn't have data as far down as other columns?
you could add a code line:
MsgBox lr
directly after the above line to see if its value is what you expect.

Completely separately, the message to the user:
MsgBox "Hi " & Application.UserName & " - Duplicate found, please check the list"
could be more helpful by giving the row number of the duplicate, for example by:
MsgBox "Hi " & Application.UserName & " - Duplicate found, please check the list at row " & cl.row

 

[jawaharprm]

Hi Sir,

in my user form there button as update, if i click that button, should refer
Sheet named "MSPP DATA" @ Column "D" there is any duplication value found it should show as "Duplicate found" and should not proceed to next.
i enclosed my excel four your reference

This is the update button code

>>> use code - tags <<<

Private Sub VPNupdate_Click()
Me.vpn = Mid(Me.partnumber, 6, 25)
End Sub

 

[p45cal]

re:

lrow = [MSPPData].Cells(Rows.Count, 1).End(xlUp).Row

there is no sheet called MSPPData. The code should be either:

lrow = Sheets("MSPP Data").Cells(Rows.Count, 1).End(xlUp).Row

or:

lrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row

For your second snippet (this looks at column N not D):

Dim MSPPData As Worksheet
Set MSPPData = Sheets("MSPP DATA")
For Each cl In MSPPData.Range("N2", MSPPData.Range("N" & Rows.Count).End(xlUp))
  If Application.CountIf(MSPPData.Range("N:N"), cl.Value) > 1 Then
    MsgBox "Hi " & Application.UserName & " - Duplicate found, please check the list"
    Exit Sub
  End If
Next cl