Checking for duplicates

  • Thread starter Thread starter The Inspector
  • Start date Start date
T

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.
 
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
 
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
Click to expand...
 
Back
Top