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

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?

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

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,
--
AP

"Abby" a écrit dans le message de
...
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?

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

I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message de
...
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?

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

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
--
AP

"Abby" a écrit dans le message de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message de
...
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?

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

Maybe I should have gone to the "new users" link. I don't know what you mean
by "worksheet code".

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message de
...
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?

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

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
--
AP

"Abby" a écrit dans le message de
...
Maybe I should have gone to the "new users" link. I don't know what you
mean
by "worksheet code".

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message de
...
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?

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

Thanks!!! That's what I needed. I appreciate your help.

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
Maybe I should have gone to the "new users" link. I don't know what you
mean
by "worksheet code".

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message de
...
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?

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

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
--
AP

"Abby" a écrit dans le message de
...
Thanks!!! That's what I needed. I appreciate your help.

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
Maybe I should have gone to the "new users" link. I don't know what
you
mean
by "worksheet code".

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message
de
...
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?

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

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?

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
Thanks!!! That's what I needed. I appreciate your help.

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
Maybe I should have gone to the "new users" link. I don't know what
you
mean
by "worksheet code".

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message
de
...
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?

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

This never happened by me...

--
AP

"Abby" a écrit dans le message de
...
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?

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
Thanks!!! That's what I needed. I appreciate your help.

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
Maybe I should have gone to the "new users" link. I don't know
what
you
mean
by "worksheet code".

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message
de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le
message
de
...
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?

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

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?

"Ardus Petus" wrote:

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
--
AP

"Abby" a écrit dans le message de
...
I want a warning to appear when I enter a duplicate code.

"Ardus Petus" wrote:

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,
--
AP

"Abby" a écrit dans le message de
...
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?