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

 

2 thoughts on “Add Data to Excel Database with UserForm using Countif Function

  1. Pingback: Add Data to Any Sheet with User Form | Excel VBA Training Online

  2. Pingback: Delete Data from Excel Worksheet Database via User Form | Excel VBA Training Online

Leave a Reply

Your email address will not be published. Required fields are marked *