Checking for Duplicate in VBA code

[vbaNumpty]

I am trying to check if the value in a combobox exists already in a column of a worksheet (Column I or 9). If the value does not exist I want to add it to the bottom of the list and filter the column A to Z, if it does I want it to do nothing.

My code seemingly is parsing the column correctly, but it is not identifying if the entry is a duplicate or not and adds it to the list anyways. Hope someone can point out what is missing.

    Dim lastRow As Long
    Dim mtchIndex As String
    Dim iCntr As Long
  
    lastRow = Fields.Range("I65000").End(xlUp).Row
  
    For iCntr = 1 To lastRow
    If Cells(iCntr, 9) <> OrderEntry.UPCBox.Value Then
        mtchIndex = "New"
    Else
        mtchIndex = "Duplicate"
    End If
    Next

Even if I use existing values the mtchindex always resolves as "new".

 

[johnnyL]

Try replacing your:

    For iCntr = 1 To lastRow
    If Cells(iCntr, 9) <> OrderEntry.UPCBox.Value Then
        mtchIndex = "New"
    Else
        mtchIndex = "Duplicate"
    End If
    Next

With the following:

MatchCheckResult = Application.WorksheetFunction.CountIf(Range("I1:I" & lastRow), OrderEntry.UPCBox.Value)
'
If MatchCheckResult > 0 Then
    mtchIndex = "Duplicate"     'Match Found
Else
    mtchIndex = "New"           'No Match
End If

 

[vbaNumpty]

Try replacing your:

    For iCntr = 1 To lastRow
    If Cells(iCntr, 9) <> OrderEntry.UPCBox.Value Then
        mtchIndex = "New"
    Else
        mtchIndex = "Duplicate"
    End If
    Next

With the following:

MatchCheckResult = Application.WorksheetFunction.CountIf(Range("I1:I" & lastRow), OrderEntry.UPCBox.Value)
'
If MatchCheckResult > 0 Then
    mtchIndex = "Duplicate"     'Match Found
Else
    mtchIndex = "New"           'No Match
End If

Awesome. Thanks a ton! Works like a charm.