VBA: Finding duplicates in Change Event

  • Thread starter Thread starter Alice
  • Start date Start date
A

Alice

I am trying to use VBA to check a new value in a cell against other
values in a column to alert the user as to whether it is a duplicate. I
would like to do this using a Message Box.

I already have code to check the length of the new value and would like
code that does something similar.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim LRange As Range

Set LRange = Range("B:B")
If Len(Target) > 27 Then
MsgBox "The value you entered is greater than 27 characters"
End If

End Sub

I am using Excel 97.

Thanx,
Alice.
 
Hi
one way: change your code as follows:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim LRange As Range
Set LRange = Range("B:B")
If Len(Target) > 27 Then
MsgBox "The value you entered is greater than 27 characters"
End If
If Application.worksheetfunction.countif(LRange,target.value)>1 then
msgbox "Duplicate"
end if
End Sub

Some note:
- I would add some more error checking to your code (checking if column
B is affected, that only one cell is changed, etc.)
- You don't need VBA for this checking for duplicates and lenght. Why
don't you use data validation. e.g. for duplicates enter the following
validation formula in 'Data - Validation' for cell B1:
=COUNTIF($B:$B,B1)=1
oor for testing the length:
=len(B1)<=27


HTH
Frank
 
Thanx for that it worked a treat!

Although I could use the Validation tool within Excel I am happier
having this code running in the background - I am supposed to be
demonstrating the amazing capabilities of VBA (mainly so I can push an
upgrade from 97!!)

Thanx again.
 
Thanx for that it worked a treat!

Although I could use the Validation tool within Excel I am happie
having this code running in the background - I am supposed to b
demonstrating the amazing capabilities of VBA (mainly so I can push a
upgrade from 97!!)

Thanx again
 
Thanx for that it worked a treat!
Although I could use the Validation tool within Excel I am happier
having this code running in the background - I am supposed to be
demonstrating the amazing capabilities of VBA (mainly so I can push an
upgrade from 97!!)

Thanx again.
Click to expand...

o.k. thats a good reason :-)
and thanks for the thanks
Frank
 
Alice,

The amazing capabilities of VBA hardly warrant an upgrade from 97 as the
improvements(!) are not exactly many or significant.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Well, for VBA, the ability to use COM addins for 2000+ is a good reason
to upgrade (I just need to learn how to make use of this).

In the workbook side, the major upgrade in 2000+ is pivot charts, but
once I had that capability, I decided I didn't need it.

Other than these, I can't think of a reason to upgrade, but then, it's
still early in the day.

- Jon
 
Back
Top