[XL-2007] Interdire doublons dans deux colonnes [Résolu]

[apdf1]

Bonjour,

En reprenant le bon code de mercatoq comment peut on interdire les doublons aux colonnes "C et D".

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, Plage As Range
 
If Target.Column = 1 And Target.Row > 5 And Target.Count = 1 Then
    Target.NumberFormat = "0##"" ""##"" ""##"" ""##"
    Set Plage = Range("A6:A" & [A65000].End(xlUp).Row)
    If Application.CountIf(Plage, Target.Value) > 1 Then
        Set c = Range("A:A").Find(Target.Text, LookIn:=xlValues, lookat:=xlWhole, After:=Target.Offset(1, 0), SearchDirection:=xlNext)
        MsgBox "Doublon en ligne " & c.Row
        Set c = Nothing
        Application.EnableEvents = False
        Target.Clear
        Application.EnableEvents = True
    End If
    Set Plage = Nothing
End If
End Sub

D'avance un grand merci

@+

Max

[Daniel.C]

Bonjour,

Le code suivant interdit les doublons pour les colonnes C ou D:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, Plage As Range
 
If (Target.Column = 3 Or Target.Column = 4) And Target.Row > 5 And Target.Count = 1 Then
    Target.NumberFormat = "0##"" ""##"" ""##"" ""##"
    Set Plage = Range(Cells(6, Target.Column), Cells(65000, Target.Column).End(xlUp))
    If Application.CountIf(Plage, Target.Value) > 1 Then
        Set c = Columns(Target.Column).Find(Target.Text, LookIn:=xlValues, lookat:=xlWhole, After:=Target.Offset(1, 0), SearchDirection:=xlNext)
        MsgBox "Doublon en ligne " & c.Row
        Set c = Nothing
        Application.EnableEvents = False
        Target.Clear
        Application.EnableEvents = True
    End If
    Set Plage = Nothing
End If
End Sub

[apdf1]

Bonjour Daniel

Je te remercie et te souhaite une bonne soirée

@+
Max