How do I make sure I don't repeat a value in my worksheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a work sheet for CPT codes, showing what was billed and what
was allowed by the insurance company. Codes are sorted in numerical order.
How do I make sure that I don't repeat one?
 
Do you want a warning to appear upon input of a duplicate code?

Or else, do you want to check for duplicates in existing codes?

Cheers,
 
Paste the following code into your Worksheet's code:

'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B"
Dim rng As Range
Dim Found As Range

Set rng = Columns(myColumn)
If Intersect(Target, rng) Is Nothing Then Exit Sub
Set Found = rng.Find(Target.Value)
If Found.Address <> Target.Address Then MsgBox ("Duplicate code")
End Sub
'-----------------------------------

HTH
 
Maybe I should have gone to the "new users" link. I don't know what you mean
by "worksheet code".
 
Select the code I gave you in my previous message
Hit Ctrl-C (copy)
Get back to Excel Window
Right-click on the tab with your worksheet name
Select "Code"
Hit Ctrl-V (Paste)
Get back to Excel Window
Try entering values in column B

HTH
 
Some improvements & bug corrections:

'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B"
Dim rng As Range
Dim Found As Range

Set rng = UsedRange.Columns(myColumn)
If Intersect(Target, rng) Is Nothing _
Or Target.Value = "" _
Then Exit Sub
Set Found = rng.Find(Target.Value)
If Found.Address <> Target.Address Then
Target.Select
MsgBox ("Duplicate code")
End If
End Sub
'-----------------------------------

HTH
 
When I came in this morning, I received a message that the macro was not
signed. I clicked OK and exited. Now I am no longer receiving a message
when I enter a duplicate code.

Any suggestions?
 
This never happened by me...

--
AP

Abby said:
When I came in this morning, I received a message that the macro was not
signed. I clicked OK and exited. Now I am no longer receiving a message
when I enter a duplicate code.

Any suggestions?
Click to expand...
 
Hi Ardus, thank you it helps, but here's one problem, when i'm deleting a
bunch of cells in the column, the macro stops and popup says"runtime
error'13', type missmatch". the reason is Target.Value is an array now, so
can't equal to one "". but i don't know how to solve it.
 
Ardus, please allow me to insert a two-fold question regarding your code
below. I've tested it and it works. I see that it checks for duplicate values
as they are being entered. However, can it be incorporated into existing VB
code to run in a macro, or, can a column of values be checked for duplicate
entries AFTER all values have been entered?
 
Back
Top