Problème colonne

Le 06/10/2016 à 00:27

Bonjour

Mon problème c'est que le programme à une plage de colonne et lorsque que rentre un deux 21 dans la colonne c6 et c7 par exemple j'ai bien le message, la c normal mais dès que je remet un 21 en d6 j'ai le message et la c pas ce que je veux

Voilà le programme

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C6:NC11")) Is Nothing Then Exit Sub

Dim macolonne As Range

Set macolonne = Range(Cells(6, Target.Column), Cells(11, Target.Column))

If Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "21nd") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "21") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "10") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "10nd") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "33") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "DE") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "Rsec") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "GTA") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "SST") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "FP") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "35") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "507i0") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "AKPS") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "AKSC") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "CGCD") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "41") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "22") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "51") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "S2") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "S4") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "S9") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "8G") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "8F") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "L4") = 2 Or _

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "R Sec") = 2 Then

UserForm2.Show

End If

End Sub

Le 06/10/2016 à 00:42

Bonsoir,

Tu ne peux pas mettre un petit fichier d'exemple avec test données ? Parce que je ne comprend pas tes explications et ne vois pas ce qui bloque.

Le 06/10/2016 à 01:12

Je pense comprendre ce que tu veux faire. Je pense que le problème vient de cette ligne :

Application.WorksheetFunction.CountIf(macolonne, "21") + Application.WorksheetFunction.CountIf(macolonne, "21") = 2 Or _

Car il suffit que tu rentres 21 dans une seule cellule de ta plage pour que le résultat soit vrai, si tu veux vraiment afficher le Userform si tu as 21 dans deux cellules de la plage, il faut que le résultat soit =4.
Le 06/10/2016 à 08:58

Bonjour,

Je verrai plus le code comme ça :

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C6:NC11")) Is Nothing Then Exit Sub

    Dim macolonne As Range
    Dim Tbl
    Dim I As Integer
    Dim OK As Boolean

    Set macolonne = Range(Cells(6, Target.Column), Cells(11, Target.Column))

    Tbl = Array("21nd", "10", "10nd", "22", "33", "35", "41", "51", "DE", "Rsec", "GTA", "SST", _
                "FP", "507i0", "AKPS", "AKSC", "CGCD", "S2", "S4", "S9", "8G", "8F", "L4", "R Sec")

    If Application.CountIf(macolonne, "21") = 2 Then OK = True

    For I = 0 To UBound(Tbl) - 1

        If Application.CountIf(macolonne, "21") + Application.CountIf(macolonne, Tbl(I)) = 2 Then

            OK = True
            Exit For

        End If

    Next I

    If OK = True Then UserForm2.Show

End Sub
Rechercher des sujets similaires à "probleme colonne"