Warning Msg with VBA

[Ekstamm]

Hi.
As Im already using validation list I cannot use validation to determin and warn for duplicates. So I need to use VBA right? And this I have no clue how to do.

Anyone help me with this?

Its D8:D19 I need to check for duplicates.
Using Excel 2011 for mac

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D8:D19")) Is Nothing Then
    If WorksheetFunction.CountIf(Range("D8:D19"), Target.Value) > 1 Then
        MsgBox "Duplicate in " & Target.Address(False, False), vbExclamation
    End If
End If
End Sub

 

[villy]

You can use conditional formating if you want to find only duplicates..
In Cond'l Formatting..
FormulaIs...
=CountIf(D$8:D$19,D8)>1

 

[Ekstamm]

Thx, Seams to work fine! Just need some tweaking.
I need to be able to duplicate "x" but no numbers.

 

[Ekstamm]

You can use conditional formating if you want to find only duplicates..
In Cond'l Formatting..
FormulaIs...
=CountIf(D$8:D$19,D8)>1

yes, but that don't give you a warning, just a colored cell right?

 

[villy]

Yes..only to show that there are dupliactes...
You can use VoG's code in VBA to give you the mesage.

 

[Ekstamm]

VoGs code works just fine for me. I just need it tweaked so that I can duplicate "x". Trying to figure out how to do it.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D8:D19")) Is Nothing Then
    If Target.Value = "x" Then Exit Sub
    If WorksheetFunction.CountIf(Range("D8:D19"), Target.Value) > 1 Then
        MsgBox "Duplicate in " & Target.Address(False, False), vbExclamation
    End If
End If
End Sub

 

[Rick Rothstein]

VoGs code works just fine for me. I just need it tweaked so that I can duplicate "x". Trying to figure out how to do it.

I'm assuming "x" is meant to stand in for any non-numeric text. Give this modification to VoG's code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D8:D19")) Is Nothing Then
        If WorksheetFunction.IsNumber(Target) Then
            If WorksheetFunction.CountIf(Range("D8:D19"), Target.Value) > 1 Then
                MsgBox "Duplicate in " & Target.Address(False, False), vbExclamation
                Target.Select
            End If
        End If
    End If
End Sub

Note that I added a Target.Select statement so that the offending duplicated number would be reselected.

 

[Ekstamm]

I'm assuming "x" is meant to stand in for any non-numeric text. Give this modification to VoG's code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D8:D19")) Is Nothing Then
        If WorksheetFunction.IsNumber(Target) Then
            If WorksheetFunction.CountIf(Range("D8:D19"), Target.Value) > 1 Then
                MsgBox "Duplicate in " & Target.Address(False, False), vbExclamation
                Target.Select
            End If
        End If
    End If
End Sub

Note that I added a Target.Select statement so that the offending duplicated number would be reselected.

That modification made the trick!
THX so much! I have been struggling for a couple of days with that.