View Full Version : Solved: Help: How to set a column Value become Key Index
yurble_vn
12-06-2007, 05:46 AM
Hi All,
Is there anyway to set a column become key index?
For example, column A, whenever I enter a number into the column, if the number is already in the above list, there will be an alert.
Thanks.
figment
12-06-2007, 06:42 AM
give this a try
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim a As Long
If Target.Column = 1 Or Target.Column = 2 Then
a = Target.Row
While Target.Offset(a - Target.Row, 0) <> "" And a <> 0
If Target = Target.Offset(a - Target.Row, 0) Then
Target = "ERROR" '<- change this to what ever error message you want
End If ' if there is no error nothing will happen
a = a - 1
Wend
End If
Application.EnableEvents = True
End Sub
right now it will run on column a and b, change add or subtract column numbers in the first if statment to change you key columns
If you are not worried about copying and pasting (which would overwrite the settings), you can use Data Validation to achieve this.
yurble_vn
12-06-2007, 10:10 AM
Hi Rory,
I also think of Validation.
But really dont know how to put it into work.
Please give some hint.
thanks all for prompt feedback
yurble_vn
12-08-2007, 11:42 AM
FOund the solution:
Use validation with custom and this formula:
(with assumption that column A is the primary key)
=countif($A:$A,A1)=1
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.