Macro challenge: verify on change that value is unique !!

[Nyborg]

I need to setup a macro to run on cell change (e.g. by using "Private Sub Worksheet_Change(ByVal Target As Range)").

Case: technician will use bar scanner to scan several batches. Some times there are two batches with the same master ID and in these cases I need the code to find the next vacant column to the right for text input

The macro needs to check that the value entered in column A is unique for that column.

• If match found then EXCEL VBA must jump the cursor to the row of the found match and find the next vacant column to the right.
• If unique, then jump to the following column in the same row.

Any suggestions are welcome.

 

[Worf]

Hi
Note that this code treats the entries as numbers, tell me if they are strings instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, fad$, i%, j%, r%


If Not Intersect(Target, Range("a:a")) Is Nothing Then
    i = 0
    With ActiveSheet.Range("a:a")
        Set c = .Find(Target.Value, LookIn:=xlValues)
        fad = c.Address
        Do
            Set c = .FindNext(c)
            If c.Value = Target.Value And c.Address <> fad Then i = 1
            r = c.Row
        Loop While Not c Is Nothing And c.Address <> fad And i = 0
    End With
    
    Select Case i
        Case Is = 0         ' unique
            Cells(Target.Row, 2).Activate
        Case Is = 1         ' match found
            j = 0
            Do
                j = j + 1
            Loop While Cells(r, 1).Offset(, j).Value <> ""
            Cells(r, j + 1).Activate    ' next vacant to the right
    End Select
End If
End Sub