Add Data to Excel Database with UserForm using Countif Function

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