Trying to delete .value but crashing?

[Susan Hayes]

Hello

Im trying to delete the value entered by the user if the value already exists in e4:n25

I have genuinely tried several ways but nothing has worked. I was able to select the value entered but when I try to
clearcontents it loops and crashes.

Thanks

Mike



Public Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit


'Application.ScreenUpdating = False
If Not Intersect(Target, Range("e4:n25")) Is Nothing Then

With Target

''''''''''''''''''''''''''''''''''''''''


Dim myarray(22, 10)

Dim lane As Single

Dim currentlane

Dim checkvalue

Dim lanevalue

Dim rng As Range

Set rng = Target.Cells

currentlane = Target.Value

Dim generalrng 'As Range



For i = 1 To 22

For j = 1 To 10

If Not (i + 3 = 14 Or i + 3 = 15) Then

generalrng = Cells(i + 3, Chr(68 + j)).Address(0, 0)

lane = Cells(i + 3, Chr(68 + j)).Value

Cells(34, "a") = lane



If Not rng.Address(0, 0) = generalrng Then

If Not (.Value = lane) Then

myarray(i, j) = Cells(i + 3, Chr(68 + j))



ElseIf .Value = lane Then '********* where the value needs to be deleted below****

Application.EnableEvents = True

Call error

Target.Cells.Select

.ClearComments





'********************************** Heres everything i have tried below*****



'Cells(i + 3, Chr(68 + j)).Select.ClearContents

'With rng.Address(0, 0).Select

Target.Cells.Select

.ClearComments

'Selection.ClearContents



'rng.Select.ClearContents

' Selection.ClearContents



' End With

'With rng.SELECT

'Selection.ClearComments

'End With

'Cells(i + 3, Chr(68 + j)).Select

'Selection.ClearNotes


Exit Sub

'*************************************************************

End If

ElseIf rng.Address(0, 0) = generalrng Then

a = i

b = j

End If

End If

myarray(a, b) = currentlane

'Cells(i + 30, Chr(68 + j)) = myarray(i, j)

Next

Next

End With

End If

ws_exit:

Application.EnableEvents = True





End Sub

 

[Dave Peterson]

There's a worksheet function that counts the number of times a value appears in
a range:

=countif(e4:n25,"asdf")
for example.

If that formula evaluates to something larger than 1, then there are multiple
values in that range.

You can use that in code like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myOverLapRng As Range
Dim myCell As Range
Dim myRngToCheck As Range

Set myRngToCheck = Me.Range("e4:n25")

Set myOverLapRng = Nothing
On Error Resume Next
Set myOverLapRng = Intersect(Target, myRngToCheck)
On Error GoTo 0

If myOverLapRng Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next 'continue processing next cell
For Each myCell In myOverLapRng.Cells
If Application.CountIf(myRngToCheck, myCell.Value) > 1 Then
MsgBox "That value already exists in: " _
& myRngToCheck.Address(0, 0)
myCell.ClearContents
End If
Next myCell
On Error GoTo 0
Application.EnableEvents = True

End Sub

 

[Ron de Bruin]

Hi Susan

Have you see Chip pearsons site
http://www.cpearson.com/excel/duplicat.htm

My Add-in have a duplicate option also
http://www.rondebruin.nl/easyfilter.htm