Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search range for duplicates
I have 5 ranges (columns) Mon Tue Wed etc. I enter names
in each range. As each name is entered I would like to search the names already entered above it and check for duplicates, then have a message box "Name already used" It's OK if I duplicate names from Mon to Tue so I want to check each day separately. Any ideas? Thanks!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
search range for duplicates
Paste the following to the sheet code module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range With Target Set Rng = Columns(.Column) If .Count = 1 Then If Application.CountIf(Rng, .Value) 1 Then ..Select ..Interior.ColorIndex = 6 MsgBox "Name already exists" ..Interior.ColorIndex = xlNone End If End If End With End Sub Regards, Greg -----Original Message----- I have 5 ranges (columns) Mon Tue Wed etc. I enter names in each range. As each name is entered I would like to search the names already entered above it and check for duplicates, then have a message box "Name already used" It's OK if I duplicate names from Mon to Tue so I want to check each day separately. Any ideas? Thanks!! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
search range for duplicates
You can remove the "If .Count = 1" line and one of
the "End If lines near the end. Regards, Greg -----Original Message----- Paste the following to the sheet code module: Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range With Target Set Rng = Columns(.Column) If .Count = 1 Then If Application.CountIf(Rng, .Value) 1 Then ..Select ..Interior.ColorIndex = 6 MsgBox "Name already exists" ..Interior.ColorIndex = xlNone End If End If End With End Sub Regards, Greg -----Original Message----- I have 5 ranges (columns) Mon Tue Wed etc. I enter names in each range. As each name is entered I would like to search the names already entered above it and check for duplicates, then have a message box "Name already used" It's OK if I duplicate names from Mon to Tue so I want to check each day separately. Any ideas? Thanks!! . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
search range for duplicates
Doug,
Try the FIND METHOD. Here's a description from VBA HELP: ----------- Find Method Finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Doesn't affect the selection or the active cell. For information about using the Find worksheet function in Visual Basic, see Using Worksheet Functions in Visual Basic. Syntax expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte) TH On 4/15/04 18:33, in article , "Doug Loewen" wrote: I have 5 ranges (columns) Mon Tue Wed etc. I enter names in each range. As each name is entered I would like to search the names already entered above it and check for duplicates, then have a message box "Name already used" It's OK if I duplicate names from Mon to Tue so I want to check each day separately. Any ideas? Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look for duplicates within a range | Excel Worksheet Functions | |||
Search for duplicates across three worksheets | Excel Worksheet Functions | |||
create a macro to search an excel spread for duplicates | Excel Worksheet Functions | |||
how do I search a colum for duplicates automatically | Excel Discussion (Misc queries) | |||
Word Search from Excel results in Duplicates | Excel Programming |