Checking for duplicates

[The Inspector]

I have a macro that takes data (name, month, amount etc..) from one worksheet
and enters it into a table on another sheet. What I want is a message box to
ask the user if he/she is sure the data needs to be entered if the name to be
entered in the name column of the table would create a duplicate in that
column, as a duplicate entry would be rare. The more likely senario would be
that the user entered the wrong month in the first sheet and needs to correct
it.
Any help is greatly appreciated.

 

[Jacob Skaria]

Something like the below...Change the <entername> to a name string or cell
reference. ws1 in the below example is Sheet2. Adjust to suit. The below code
checks whether the name already exists in Sheet2 Column A. If exists the
procedure exists. or otherwise will continue

Sub Macro
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Sheet2")

If WorksheetFunction.CountIf(ws1.Range("A:A"), "<entername>") > 0 Then
If MsgBox("This name already exists. Are you sure to copy ?", vbYesNo + _
vbQuestion + vbDefaultButton2) <> vbYes Then Exit Sub
End If

'Your code to copy continue here

End Sub

If this post helps click Yes

 

[Bernd P]

Hello,

A worksheet function countif approach is suboptimal if you need to
check a large amount of data. Please see an analysis of an analogous
case:
http://www.sulprobil.com/html/count_unique.html

I would suggest some code fragment similar to my UDFs Pstat or Pfreq:
http://www.sulprobil.com/html/pstat.html
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd

 

[The Inspector]

Thanks.

Something like the below...Change the <entername> to a name string or cell
reference. ws1 in the below example is Sheet2. Adjust to suit. The below code
checks whether the name already exists in Sheet2 Column A. If exists the
procedure exists. or otherwise will continue

Sub Macro
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Sheet2")

If WorksheetFunction.CountIf(ws1.Range("A:A"), "<entername>") > 0 Then
If MsgBox("This name already exists. Are you sure to copy ?", vbYesNo + _
vbQuestion + vbDefaultButton2) <> vbYes Then Exit Sub
End If

'Your code to copy continue here

End Sub

If this post helps click Yes