Duplicate

L

levski

Board Regular
Joined
Sep 15, 2004
Messages
85
Hello

Within an on_change sub, how would I check the changed cell against range a1:10 for a duplicate entry?

Thank you
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Try this:
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CheckRange      As Range
Dim Count           As Long

    Set CheckRange = Range("A1:A10")
    Count = Application.WorksheetFunction.CountIf(CheckRange, Target.Text)
    If Count > 0 Then
        MsgBox Target.Text & " is in the range " & CheckRange.Address(False, False)
    Else
        MsgBox Target.Text & " is not in the range " & CheckRange.Address(False, False)
    End If

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Exit Sub when two duplicates values found
Replies
2
Views
191
Fuji
F
G
  • Question
Clean duplicates but keep the rows
Replies
4
Views
78
Gabriell
G
N
  • Solved
Formula to flag duplicates ignoring initial entry
2
Replies
16
Views
419
NikiDivi
N
O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
434
OilEconomist
O
P
  • Question
Duplicates and Combining multiple cells into 1 cell
Replies
6
Views
435
Patriot2879
P

Forum statistics

Threads
1,221,668
Messages
6,161,158
Members
451,687
Latest member
KENNETH ROGERS

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top