Unique record

  • Thread starter Thread starter ×לי
  • Start date Start date
×

×לי

Hi!

I have a table with 100 records and 10 fields. My problem is how to verify
programmaticaly that when I am adding a new record it must be unique and no
other record in the table has the same combination of values.

Thanks in advance

Eli
 
Thanks Joel for your fast response.

The code you wrote contains two "Next ColCount". Maybe one of them (the last
one) is actually "Next RowCount"?

Eli
 
This macro assumes that:

1. the data is in columns A thru J
2. data is entered from the bottom
3. once a row is "complete" there will be no blanks in A thru J

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long, t As Range, r1 As Range
Dim ValueOfRow As String, TestValue As String
Set t = Target
rw = Target.Row
Set r1 = Range("A" & rw & ":J" & rw)

If Intersect(t, r1) Is Nothing Then Exit Sub
If Application.WorksheetFunction.CountBlank(r1) > 0 Then Exit Sub

If rw = 1 Then Exit Sub
ValueOfRow = ""
For i = 1 To 10
ValueOfRow = ValueOfRow & Cells(rw, i).Value
Next

For j = 1 To rw - 1
TestValue = ""
For i = 1 To 10
TestValue = TestValue & Cells(j, i).Value
Next
If TestValue = ValueOfRow Then
MsgBox "row " & rw & " matches row " & j
Exit Sub
End If
Next
MsgBox "row " & rw & " is unique"
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Back
Top