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
You air sir very gerad
And very Nice
Ticher
I like you
But i am not very herd Eniglish .
I am class only 8th for my eig 39