• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Issues in Duplicate Value should not update in userform

Hi All,

Code:
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

Code:
'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

Well-Known Member
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
 
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 <<<
Code:
Private Sub VPNupdate_Click()
Me.vpn = Mid(Me.partnumber, 6, 25)
End Sub
 

Attachments

p45cal

Well-Known Member
re:
Code:
lrow = [MSPPData].Cells(Rows.Count, 1).End(xlUp).Row
there is no sheet called MSPPData. The code should be either:
Code:
lrow = Sheets("MSPP Data").Cells(Rows.Count, 1).End(xlUp).Row
or:
Code:
lrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
For your second snippet (this looks at column N not D):
Code:
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
 
Top