Avoid duplicates RRS feed

  • Question

  • I have a data entry form to register projects numbers and names in an Excel sheet, column (A) for number, column (B) for name. 

    When I enter the project No.,I want VBA code to check that the number is not already assigned for another project.

    I used VLOOK() function. But it give me an error when the number is not there.

    Friday, January 25, 2019 9:34 AM

All replies

  • Use Countif to check whether the project number exists or not in colunm A.

    If Application.CountIf(Worksheets("Sheet1").Range("A:A"), <ProjectNumber>) = 0 Then
        'Code to write the data on the sheet
        MsgBox "The Project Number already exists.", vbExclamation
        Exit Sub
    End If

    In the above code replace <ProjectNumber> with the variable which holds the project number.

    Subodh Tiwari (Neeraj) sktneer

    Saturday, January 26, 2019 3:24 AM