none
[Excel] Avoiding duplicates RRS feed

  • Question

  • I am trying to use a userform to add data to a specific columns, but it seems to detect error of the duplicate, but does not allow to add data if I correctly enter the ITS details: here's the code

    Private Sub CommandButton1_Click()
    
    Dim dupli As Long
    Dim itsval As Long
    Dim yippie1 As Long
    
    itsval = Val(Me.its.Value)
    dupli = Application.WorksheetFunction.Match(itsval, ThisWorkbook.Worksheets("Master").Range("B2:B10000"), 0)
    If dupli > 0 Then
    MsgBox "Duplicate ITS entered", vbCritical
    Exit Sub
    Else
    With ThisWorkbook.Worksheets("Master")
    yippie1 = Worksheets("master").Cells(Rows.Count, "B").End(xlUp).Row
    Cells(yippie1 + 1, 2).Value = its.Value
    Cells(yippie1 + 1, 3).Value = sabil.Value
    Cells(yippie1 + 1, 5).Value = reg.Value
    Cells(yippie1 + 1, 6).Value = name1.Value
    Cells(yippie1 + 1, 7).Value = dob.Value
    Cells(yippie1 + 1, 8).Value = nation.Value
    Cells(yippie1 + 1, 9).Value = watan.Value
    Cells(yippie1 + 1, 19).Value = mobile.Value
    Cells(yippie1 + 1, 22).Value = email.Value
    Cells(yippie1 + 1, 27).Value = nomi1.Value
    Cells(yippie1 + 1, 28).Value = nomi2.Value
    Cells(yippie1 + 1, 29).Value = nomi3.Value
    Cells(yippie1 + 1, 30).Value = nomi4.Value
    
    Call UserForm_Initialize
    
    'Dim yippie2 As Long
    'Worksheets("Summary").Activate
    'yippie2 = Worksheets("summary").Cells(Rows.Count, "B").End(xlUp).Row
    'ActiveSheet.Cells(yippie2 + 1, 2).Value = its.Value
    End With
    End If
    End Sub

    Any help is appreciated

    Sunday, April 12, 2015 6:01 AM

Answers

  • But now, I would like to audit the ITS characters to be exactly 8 characters long. How do I achieve that ??

    Most people stumble at this point because they try to use formula funcitons in VBA, which is possible but usually not easy. The issue with this functions is the different data typ (it don't like to get into the details why), but in fact 123 (a real number) is not equal "123" (a string of characters).

    Especially if the function causes an error the code no longer works because a correct error handling in VBA is even harder.

    An easier way is to use the functionality of VBA, in this case RANGE.FIND, which is nearly the same as a manual search.
    The FIND function returns a RANGE object if the term is found (the RANGE object tells you where) or it returns NOTHING.

    And you can define how and where FIND should search.

    So in your case you have to search for a VALUE and you have to compare the WHOLE cell (instead of a part).

    Private Sub CommandButton1_Click()
      Dim R As Range
      Dim This As Variant
      'Get the value
      This = its.Value
      'Search in column B
      Set R = Range("B:B").Find(This, LookIn:=xlValue, LookAt:=xlWhole)
      'Found?
      If Not R Is Nothing Then
        MsgBox "Duplicate found in row " & R.Row
        Exit Sub
      End If
     
      'Rest of your code here
    End Sub


    When you try that you'll see that there is no issue to find 123 with "123", as well as in a manual search.

    Now to your question, how to make the ITS.Value 8 chars long... there are many ways. VBA gives you MID, LEFT, RIGHT to copy parts of a string, so when you want to "fill" the left side of the value with zeros, this is an easy one:

    ItsVal = Right(String(8, "0") & its.Value, 8)

    Have a look into the VBA help for more details (place the cursor on RIGHT or FIND or STRING and press F1).

    BTW, there is also an easier way to store / load data from / into a Userform, have a look to my answers into this thread:
    https://social.msdn.microsoft.com/Forums/de-DE/e013ac7d-7d0c-4c1d-af06-8b3b2ad8cc25/excel-2010-userform-vba-how-to-stop-job-no-from-duplicating-itself-on-next-empty-row

    Andreas.
    • Marked as answer by L.HlModerator Monday, April 20, 2015 9:16 AM
    Sunday, April 12, 2015 9:11 AM

All replies

  • Seems I got by working the other way around using this code

    Private Sub CommandButton1_Click()
    Dim yippie1 As Long
    
    yippie1 = Worksheets("master").Cells(Rows.Count, "B").End(xlUp).Row
    
    If WorksheetFunction.CountIf(Worksheets("Master").Range("B:B"), its.Value) = 0 Then
    
    Cells(yippie1 + 1, 2).Value = its.Value
    Cells(yippie1 + 1, 3).Value = sabil.Value
    Cells(yippie1 + 1, 5).Value = reg.Value
    Cells(yippie1 + 1, 6).Value = name1.Value
    Cells(yippie1 + 1, 7).Value = dob.Value
    Cells(yippie1 + 1, 8).Value = nation.Value
    Cells(yippie1 + 1, 9).Value = watan.Value
    Cells(yippie1 + 1, 19).Value = mobile.Value
    Cells(yippie1 + 1, 22).Value = email.Value
    Cells(yippie1 + 1, 27).Value = nomi1.Value
    Cells(yippie1 + 1, 28).Value = nomi2.Value
    Cells(yippie1 + 1, 29).Value = nomi3.Value
    Cells(yippie1 + 1, 30).Value = nomi4.Value
    
    Call UserForm_Initialize
    Else
    MsgBox "ITS value exists. Please recheck ID"
    
    'Dim yippie2 As Long
    'Worksheets("Summary").Activate
    'yippie2 = Worksheets("summary").Cells(Rows.Count, "B").End(xlUp).Row
    'ActiveSheet.Cells(yippie2 + 1, 2).Value = its.Value
    End If
    End Sub
    But now, I would like to audit the ITS characters to be exactly 8 characters long. How do I achieve that ??

    Sunday, April 12, 2015 6:39 AM
  • But now, I would like to audit the ITS characters to be exactly 8 characters long. How do I achieve that ??

    Most people stumble at this point because they try to use formula funcitons in VBA, which is possible but usually not easy. The issue with this functions is the different data typ (it don't like to get into the details why), but in fact 123 (a real number) is not equal "123" (a string of characters).

    Especially if the function causes an error the code no longer works because a correct error handling in VBA is even harder.

    An easier way is to use the functionality of VBA, in this case RANGE.FIND, which is nearly the same as a manual search.
    The FIND function returns a RANGE object if the term is found (the RANGE object tells you where) or it returns NOTHING.

    And you can define how and where FIND should search.

    So in your case you have to search for a VALUE and you have to compare the WHOLE cell (instead of a part).

    Private Sub CommandButton1_Click()
      Dim R As Range
      Dim This As Variant
      'Get the value
      This = its.Value
      'Search in column B
      Set R = Range("B:B").Find(This, LookIn:=xlValue, LookAt:=xlWhole)
      'Found?
      If Not R Is Nothing Then
        MsgBox "Duplicate found in row " & R.Row
        Exit Sub
      End If
     
      'Rest of your code here
    End Sub


    When you try that you'll see that there is no issue to find 123 with "123", as well as in a manual search.

    Now to your question, how to make the ITS.Value 8 chars long... there are many ways. VBA gives you MID, LEFT, RIGHT to copy parts of a string, so when you want to "fill" the left side of the value with zeros, this is an easy one:

    ItsVal = Right(String(8, "0") & its.Value, 8)

    Have a look into the VBA help for more details (place the cursor on RIGHT or FIND or STRING and press F1).

    BTW, there is also an easier way to store / load data from / into a Userform, have a look to my answers into this thread:
    https://social.msdn.microsoft.com/Forums/de-DE/e013ac7d-7d0c-4c1d-af06-8b3b2ad8cc25/excel-2010-userform-vba-how-to-stop-job-no-from-duplicating-itself-on-next-empty-row

    Andreas.
    • Marked as answer by L.HlModerator Monday, April 20, 2015 9:16 AM
    Sunday, April 12, 2015 9:11 AM