How to Avoid Duplicate Entries in Excel Worksheet While Transferring Data Via UserForm


How can a user avoid duplicate entries in her Excel worksheet while transferring data via a user-form. We can use a looping process or use the ‘Range.Find‘ method. The latter is quite efficient.

The syntax of the Find method is:

Expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Expression represents a Range object and in our VBA code it is represented by the variable ‘FoundCell’.

Watch the training video:


Let’s look at each of the parameters used by the Find method:

1. What: This the only required parameter. It represents the data we wish to search for. The data type for this parameter can be a string, integer, date or any other Microsoft Excel data type.

2. After (optional parameter): It is an optional parameter and represents the cell after which you wish to search for the data. You can define it as ‘B1’ or ‘ActiveCell’ but it generally assumes that you wish to search after the active cell i.e. after the cell where the cursor is located. If we do not specify the cell, the search starts after the cell in the left top corner of the range.

3. LookIn (optional parameter): Expresses the type of information we are looking for. It can be formulas (xlFormulas), values (xlValues), etc.

4. LookAt (optional parameter): This parameter defines whether we look at all the data or part of the data – using xlPart or xlWhole

5. SearchOrder (optional): We can define whether to search by rows (xlRows) or by columns (xlColumns).

6. SearchDirection (optional): This parameter searches for the next values (xlNext) or the previous values (xlPrevious).

7. MatchCase (optional): The match case parameter, if set to True, will differntiate between ‘A’ and ‘a’. For example, ‘Arnold’ will not be treated the same as ‘arnold’. The default value is set to FALSE.

8. MatchByte (optional): This parameter can have two values – True or False. It can be used if your language is Japanese or Chinese. The default value of the parameter is set to false.

9. SearchFormat (optional): You can search for data based on its format. For example, we could search for data that is ‘bold’ or in ‘italics’. We could also search for data based on the font style like ‘Courier’, ‘Times New Roman’, ‘Ariel’, etc.

The return value would then give us the first cell where our searched data is located.

The complete VBA code:

Private Sub cmdClear_Click()
txtID.Text = “”
txtFName.Text = “”
txtLName.Text = “”
txtID.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdTransfer_Click()
Dim FoundCell As Range
Dim Search As String
Dim eRow As Long
eRow = Worksheets(“Data”).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Search = txtID.Text
Set FoundCell = Worksheets(“Data”).Columns(1).Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
If FoundCell Is Nothing Then
MsgBox “No existing ID matches the entered ID!”
Cells(eRow, 1).Value = txtID.Text
Cells(eRow, 2).Value = txtFName.Text
Cells(eRow, 3).Value = txtLName.Text

Else
MsgBox “ID exists!” & ” data found at cell address ” & FoundCell.Address
End If
End Sub

Private Sub UserForm_Initialize()
txtID.SetFocus
End Sub

Further reading:

http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx

http://www.rondebruin.nl/win/s9/win006.htm

Download a sample file by clicking on the Excel icon: