How to add data to an Excel database with userform using the countif worksheet function. Last time we learnt how to add data to a database in Excel using a ‘for next’ loop. The countif function helps to avoid duplicate entries in our databse.

Watch the video below:

Watch this video on YouTube.

Here’s the complete VBA code:

Dim currentrow As Long

Private Sub UserForm_Initialize()
currentrow = 2
TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)

End Sub

Private Sub cmdAdd_Click()
Dim lastrow
lastrow = Sheet1.Cells(Rows.count, 1).End(xlUp).Row
lastrow = lastrow + 1
Cells(lastrow, 1) = TextBox1
If Application.WorksheetFunction.CountIf(Range(“A2:A” & lastrow), Cells(lastrow, 1)) > 1 Then
MsgBox “Duplicate data”, vbCritical, “Remove Data”
Cells(lastrow, 1) = “”
ElseIf Application.WorksheetFunction.CountIf(Range(“A2:A” & lastrow), Cells(lastrow, 1)) = 1 Then
answer = MsgBox(“Are you sure you want to add the record?”, vbYesNo + vbQuestion, “Add Record”)
If answer = vbYes Then
Cells(lastrow, 1) = TextBox1.Text
Cells(lastrow, 2) = TextBox2.Text
Cells(lastrow, 3) = TextBox3.Value
End If
End If

End Sub