Check if data is already exist before adding a new data

[cjvdg]

Hello! So, I'm currently having a problem in my data entry. Just want to ask if how can I avoid duplicates? So on my worksheet, I have an ADD sheet that the user is going to use to enter the data. Then I have the DATA sheet where all of the data is. The unique key is the STUDENT ID. Thank you in advance!

[mancubus]

Sub add_stud()

If Application.CountIf(Worksheets("Data").Columns(3), Range("a_studID").Value) > 0 Then
MsgBox "Existing student ID in database! Quitting...", vbOKOnly, "Duplicate entry!"
Exit Sub
End If

With Worksheets("Data")
next_row = .Range("C" & .Rows.Count).End(xlUp).Offset(1).Row
.Cells(next_row, 3).Value = Range("a_studID").Value
.Cells(next_row, 4).Value = Range("a_lname").Value
.Cells(next_row, 5).Value = Range("a_fname").Value
.Cells(next_row, 6).Value = Range("a_ext").Value
.Cells(next_row, 7).Value = Range("a_mname").Value
End With

ThisWorkbook.Save

MsgBox "Data has been saved!", vbOKOnly + vbInformation, "SF10"

End Sub

[cjvdg]

Sub add_stud()

If Application.CountIf(Worksheets("Data").Columns(3), Range("a_studID").Value) > 0 Then
MsgBox "Existing student ID in database! Quitting...", vbOKOnly, "Duplicate entry!"
Exit Sub
End If

With Worksheets("Data")
next_row = .Range("C" & .Rows.Count).End(xlUp).Offset(1).Row
.Cells(next_row, 3).Value = Range("a_studID").Value
.Cells(next_row, 4).Value = Range("a_lname").Value
.Cells(next_row, 5).Value = Range("a_fname").Value
.Cells(next_row, 6).Value = Range("a_ext").Value
.Cells(next_row, 7).Value = Range("a_mname").Value
End With

ThisWorkbook.Save

MsgBox "Data has been saved!", vbOKOnly + vbInformation, "SF10"

End Sub


It worked! Thank you so much!

[mancubus]

you are welcome.