Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a 10 item Mail Merge repeat itself? | Excel Discussion (Misc queries) | |||
how do I make the first three rows repeat on top of every page? | New Users to Excel | |||
How to make repeat long numbers shortcut ? | Excel Worksheet Functions |