Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent entering duplicate cell data in a column
Hi,
I am struggling to find a method prevent users entering duplicate data to a coulumn. The requirement is quite simple -- check if the same cell value is already found in the column to which data is currently being entered. For instance, when entering data to cell A8, I need to check if the the same value is already found in the cells ranging from A2 to A20. This is just to prevent users entering dupliate data. Could someone kindly give me any sample code or hint ? Thank you very much Samantha |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent entering duplicate cell data in a column
Samantha,
Highlight A2:A20 From Data | Validation: Allow = Custom Formula: =COUNTIF($A$2:$A$20,A2)=1 Rob "Samantha James" wrote in message om... Hi, I am struggling to find a method prevent users entering duplicate data to a coulumn. The requirement is quite simple -- check if the same cell value is already found in the column to which data is currently being entered. For instance, when entering data to cell A8, I need to check if the the same value is already found in the cells ranging from A2 to A20. This is just to prevent users entering dupliate data. Could someone kindly give me any sample code or hint ? Thank you very much Samantha |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent entering duplicate cell data in a column
Hi
Put this into the code module behind the sheet where you are inputting the data The IsEmpty check and the "On Error Resume Next" parts of the code are to deal with the very first cell. Private Sub Worksheet_Change(ByVal Target As Range) Dim ColumnVariant As Variant Dim i As Long, Counter As Integer Dim RangeSize As Long Dim TestValue As Variant ColumnVariant = Range(Range("A1"), Range("A65536").End(xlUp)).Value If IsEmpty(ColumnVariant) Then Exit Sub TestValue = Target.Value On Error Resume Next RangeSize = UBound(ColumnVariant, 1) On Error GoTo 0 If RangeSize = 0 Then Exit Sub For i = 1 To RangeSize If TestValue < "" Then If ColumnVariant(i, 1) = TestValue Then Counter = Counter + 1 End If If Counter = 2 Then MsgBox "Value already exists in Column A" Target.Clear Target.Activate Exit Sub End If Next i End Sub regards Paul (Samantha James) wrote in message . com... Hi, I am struggling to find a method prevent users entering duplicate data to a coulumn. The requirement is quite simple -- check if the same cell value is already found in the column to which data is currently being entered. For instance, when entering data to cell A8, I need to check if the the same value is already found in the cells ranging from A2 to A20. This is just to prevent users entering dupliate data. Could someone kindly give me any sample code or hint ? Thank you very much Samantha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prevent a user from entering data in a cell? | Excel Discussion (Misc queries) | |||
Prevent duplicate data from being entered into the same column | Excel Worksheet Functions | |||
How do I prevent entering duplicate numbers in an excel worksheet | Excel Worksheet Functions | |||
How do you Stop Entering Duplicate Data in a Column? | Excel Worksheet Functions | |||
How do I prevent duplicate numbers from being entered in a column. | Excel Discussion (Misc queries) |