VBA: Finding duplicates in Change Event

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.
 
F

Frank Kabel

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
 
A

Alice

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.
 
A

Alice

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
 
F

Frank Kabel

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
 
B

Bob Phillips

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)
 
J

Jon Peltier

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
 
Top