VBA: Finding duplicates in Change Event
 

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.


---
Message posted from http://www.ExcelForum.com/

Finding duplicates in Change Event
 

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,targe t.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

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.


---
Message posted from http://www.ExcelForum.com/

VBA: Finding duplicates in Change Event
 

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.


---
Message posted from http://www.ExcelForum.com/

VBA: Finding duplicates in Change Event
 

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

--
Message posted from http://www.ExcelForum.com

VBA: Finding duplicates in Change Event
 

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.

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

VBA: Finding duplicates in Change Event
 

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)

"Alice " wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

VBA: Finding duplicates in Change Event
 

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Bob Phillips wrote:

Alice,

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