Do not duplicate number

[Wanna Learn]

Hello I have a long spreadsheet that has a column with extension numbers. I
do not want the same extension to be entered twice , (not 2 people can have
the same extension number) Is it possible to have an error message pop up
when a duplicate extension number is entered in the spreadsheet? I have
excel 2002 thanks in advance

 

[Stefi]

What about Custom Data validation with this formula:
=COUNTIF(A:A,A1)=1
A being the column containing your extension numbers. Select column A and
create the above Custom Data validation with appropriate message!
Regards,
Stefi


„Wanna Learn†ezt írta:

 

[Thomas [PBD]]

You can also use a Macro to do the validation check:

Sub Ext_Check()
Dim xlssheet As Excel.Worksheet
Dim x As Long
Dim y As Long
Dim rng As String
Dim val As String
Dim loc As String
Dim msgstr As String

Set xlssheet = Excel.ActiveSheet

rng = InputBox("Enter Range of Cells (i.e. A1:A10)", "Range")

xlssheet.Range(rng).Select
y = Selection.Cells.Count

Do Until ActiveCell.Value = ""
val = ActiveCell.Value
loc = ActiveCell.Address
Do Until ActiveCell.Value = ""
For x = 1 To y
Selection.Cells(x, 1).Activate
If ActiveCell.Address <> loc Then
If ActiveCell.Value = val Then
msgstr = "Extension currently in use. Please double check." & vbCrLf & "(" &
loc & "=" & ActiveCell.Address & ")"
MsgBox msgstr, vbOKOnly, "Error"
Exit Sub
End If
End If
Next
Loop
Loop
End Sub

 

[Thomas [PBD]]

My apologies, I have coded this so that it has got to find an error in the
first line... amended:

Sub Ext_Check()
Dim xlssheet As Excel.Worksheet
Dim x As Long
Dim y As Long
Dim rng As String
Dim val As String
Dim loc As String
Dim msgstr As String

Set xlssheet = Excel.ActiveSheet

rng = InputBox("Enter Range of Cells (i.e. A1:A10)", "Range")

xlssheet.Range(rng).Select
y = Selection.Rows.Count
x = 1

For x = 1 To y + 1
Selection.Cells(x, 1).Activate
val = ActiveCell.Value
loc = ActiveCell.Address
Selection.Cells(x + 1, 1).Activate
If ActiveCell.Address <> loc Then
If ActiveCell.Value = val Then
msgstr = "Extension currently in use. Please double check." & vbCrLf & "(" &
loc & "=" & ActiveCell.Address & ")"
msgbox msgstr, vbOKOnly, "Error"
Exit Sub
End If
End If
Next
End Sub

--
--Thomas [PBD]
Working hard to make working easy.

You can also use a Macro to do the validation check:

Sub Ext_Check()
Dim xlssheet As Excel.Worksheet
Dim x As Long
Dim y As Long
Dim rng As String
Dim val As String
Dim loc As String
Dim msgstr As String

Set xlssheet = Excel.ActiveSheet

rng = InputBox("Enter Range of Cells (i.e. A1:A10)", "Range")

xlssheet.Range(rng).Select
y = Selection.Cells.Count

Do Until ActiveCell.Value = ""
val = ActiveCell.Value
loc = ActiveCell.Address
Do Until ActiveCell.Value = ""
For x = 1 To y
Selection.Cells(x, 1).Activate
If ActiveCell.Address <> loc Then
If ActiveCell.Value = val Then
msgstr = "Extension currently in use. Please double check." & vbCrLf & "(" &
loc & "=" & ActiveCell.Address & ")"
MsgBox msgstr, vbOKOnly, "Error"
Exit Sub
End If
End If
Next
Loop
Loop
End Sub

--
--Thomas [PBD]
Working hard to make working easy.

Hello I have a long spreadsheet that has a column with extension numbers. I
do not want the same extension to be entered twice , (not 2 people can have
the same extension number) Is it possible to have an error message pop up
when a duplicate extension number is entered in the spreadsheet? I have
excel 2002 thanks in advance