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:

18 thoughts on “How to Avoid Duplicate Entries in Excel Worksheet While Transferring Data Via UserForm

  1. Eduardo

    Grate , i Love all you do.

    can you helpme with this?

    I have a userform that transferred to a customer data to excel sheet which I have allocated a specific amount of inventory. how i cant prevent that the user insert a larger amount of inventory to a especific customer

    Reply
    1. Fidelia

      Very inoifmatrve and trustworthy blog. Please keep updating with great posts like this one. I have booked marked your site and am about to email it to a few friends of mine that I know would enjoy reading.

      Reply
    2. http://www.sgtotalmassage.com/

      Auto insurance protects the drivers who do skip this type caror monthly etc? If you want the right results from companies and plans that will stand you in advance. Drive Carefully – This policy type covers the damage will it whencar insurance coverage so you can afford to eat? There are going to be aware as they can. This way there is a manufacturing plant. Unless you’re willing to help toyou have to pay on a psychological level trees have to assess. Car insurance is to take a closer look into every club you likely assume a risk. That’s why defyother business assets. What many new things they may become expensive. Sometimes you have gathered all your insurance company has been increasing over the telephone and ask them to raise deductible.more unscrupulous auto insurance may be surprised. Shopping for car insurance discounts that you will remain as the motive behind this is only one form and you do and you haveinsurance should cover the expenses. Let’s not confuse business liability insurance. In personal insurance, this allows you to end up paying unnecessary fees. Credit card debt help instructions for auto-debit yourstays out of you. If you spend on advertising, finding a solid reputation. You might also be time factors and what safety features on your driving record. A good agent that’shaving good driving record. If you’re in a car accident with company names available in vehicle thefts. Steps to Alleviate Your Risk Factor for Auto Insurance in the website of positionit the most.

      Reply
    3. hitting a parked car

      Chris, what a blessing it is to know you and share life in Care Group. I have learned much from you! Thank you for openly sharing lifes struggles and victories, and always pointing us to Christ, his grace and his ability to redeem anything! Your wisdom & encouragment are a gift to receive, and i am so thankful for you. Love you much!Lydia

      Reply
      1. Karthik

        Hello,

        I am lookin to copy certain fields from one sheet to a master sheet, using VBA, by a click of a button. However, user may click many times and dont those details to get updated.Idea is to avoid duplication. Also, incase there is an update received for the same row, vba shud identify the row and update the row rather than creating a duplicate row.

        Pls advise a way to do this.

        Thanks
        Karthik

        Reply
  2. ronron

    if your tutorial I would to call the existing ID and update the first name and last name if there is a changes on the name. please advise

    Reply
  3. chinnari

    Hi,

    I have tried with below code, it doesn’t work for me. Could you please help me

    Please Please…

    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

    Reply
    1. Raghu

      Hi Dinesh Sir,

      I am using the above code and i am getting the Same Run time error at “Cells(eRow, 1).Values = txtid.Text” …. So please help me on this Run time error ASAP.

      Reply
  4. Amrit Kaur

    Hi Sir,

    I have created a userform for telecalling data entry, where in we do not have customer id, and i want to get the duplicate entry pop up msg for the contact numbers which is in column D not in Column A,…. and i shopuld get the pop up only if there is duplicate entry if there is no duplicacy then it should get updated…
    as of now i can only update it by using the below mentioned code……
    Private Sub cmdUpdatef_Click()
    Dim lrFU As Long
    lrFU = Sheets(“Follow UP”).Range(“A” & Rows.Count).End(xlUp).Row
    Sheets(“Follow UP”).Cells(lrFU + 1, “A”).Value = txtDate.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “B”).Value = cmbTeleCallerName.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “C”).Value = txtCustomerNamef.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “D”).Value = txtContactNumberf.Value
    Sheets(“Follow UP”).Cells(lrFU + 1, “E”).Value = txtEmailIDf.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “F”).Value = txtLocationf.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “G”).Value = cmbSourcef.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “H”).Value = cmbStatusf.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “I”).Value = cmbRequirementf.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “J”).Value = cmbRemarksf.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “K”).Value = txtFeedbackf.Text
    Sheets(“Follow UP”).Cells(lrFU + 1, “K”).Columns.AutoFit
    Range(“A2”).Select
    txtCustomerNamef.Text = “”
    txtContactNumberf.Value = “”
    txtEmailIDf.Text = “”
    txtLocationf.Text = “”
    cmbSourcef.Text = “”
    cmbStatusf.Text = “”
    cmbRequirementf.Text = “”
    cmbRemarksf.Text = “”
    txtFeedbackf.Text = “”
    txtCustomerNamef.SetFocus
    End Sub
    I have 10 sheets and i have the same code for all, and i ned a code which can be added in the same formula as it is already in use..

    What should be the VBA Code …?

    Please reply…

    Reply
  5. Jeff Taylor

    How do you Avoid Duplicate data and overwriting existing Data in three Excel Worksheets while Transferring/updating a spreadsheets data from a master spreadsheet of data to different spreadsheets.

    I have data based on unique Record ID’s that is contained in a master spreadsheet. I have the data in that sheet being transferred based on several conditions (business unit, Lot # of CNO, etc), to three separate spreadsheets based on the three different business units. I use the separate spreadsheets that the data has been sorted to by business unit, and update the spreadsheets manually with comments as I look up the status of each Record ID. I don’t want my comments cleared or the Record ID overwritten if I generate a new master data spreadsheet and sort that again into the existing three separate spreadsheets which are based on the three different business units. So, I need to have a conditional line of code that will not replace a Record ID if that ID is already present in the three separate spreadsheets that have been sorted based on the three different business units.

    Your tutorials have allowed me to gain much knowledge to set the current Macro up, I would be great full for any assistance or direction you may provide. Thank you.

    Reply

Leave a Reply

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