VBA countif conditions multiples

Bonjour,

ceci est mon premier message sur le forum, donc d'avance bonjour à tous, merci pour ce forum que je fréquente avec plaisir en apprenti depuis quelques temps, et merci d'avance pour toute aide !

Je bricole en VBA depuis quelques temps, j'ai donc quelques notions, mais qui restent largement à compléter.

Voici ma question, pour la facilité j'ai joint un fichier avec des données exemple et la problématique.

Je dois numéroter les lignes de mon tableau en fonction de 2 critères.

Le premier: En colonne A, j'ai différents éléments (texte), chaque élément doit être numéroté en B de façon incrémentielle, que les occurrences soient adjacentes ou non. J'ai joint un fichier simple qui montre le principe de ce que je cherche à faire. Jusque là, j'ai réussi, avec le code suivant (trouvé sur les forums):

Sub Numerote()
Dim DerLig As Long

  DerLig = Range("A" & Rows.Count).End(xlUp).Row 'définit la plage de données dans laquelle on opére (jusqu'à la dernière ligne remplie)
  With Range("C2:C" & DerLig) 'définit la colonne à remplir avec le compteur
    .Formula = "=COUNTIF(A$2:A2,A2)" 'colle une formule dans C qui correspond a un nb.si (mêmes arguments)sur la base des éléments trouvés en A (ici, pomme, avocat...)

  End With
End Sub

Ce code fait ce qu'il doit faire. MAIS j'ai une deuxième condition à remplir: Certaines lignes contiennent des doublons en colonne D. Lorsqu'il y a un doublon d'une ligne à l'autre pour un même élément en A (et dans ce cas seulement), il ne faut pas incrémenter, mais garder le numéro précédent (cf. code couleur dans le fichier exemple).

J'ai modifié le code avec une boucle if, mais je n'ai pas pu tester si elle fait son boulot, pq il doit y avoir une erreur fondamentale, elle ne s'arrête pas. Voici le bout de code supplémentaire (avec un Dim candidat as Integer) :

For Candidat = 2 To DerLig
    If Cells(Candidat, 4) <> Cells(Candidat, 4).Offset(-1, 0) Then
        'insérer ici la formule countif ?
    Else
        'quoi mettre ici ?
    End If
Next Candidat

Quelques précisions: Le code que j'ai trouvé crée une formule dans le tableau. Peut-on faire autrement/ plus élégant/économique ?

Dans une deuxième étape, j'ai besoin de concaténer le résultat avec la colonne A. J'ai aussi une macro pour ça:

Sub ConcatCols()
     'concatenate columns A & C in column E

    Dim DerLig As Long

        DerLig = Range("A" & Rows.Count).End(xlUp).Row 'définit la plage de données dans laquelle on opére (jusqu'à la dernière ligne remplie)
        With Range("E2:E" & DerLig)
            .Formula = "=A2 & C2"
            .Value = .Value
        End With

End Sub

Je me dis qu'il doit y avoir moyen de combiner les deux directement? Avoir donc comme résultat directement la colonne B (souhaité) de mon tableau ? Mais je ne sais pas à quel endroit les imbriquer, les essais n'étaient pas concluant (rien ne se passait).

Ne trouvant pas la solution, je m'adresse au forum. J'espère avoir été assez claire et pas trop long ! Merci d'avance pour votre intérêt et votre temps.

180exemple-num-auto.xlsm (18.13 Ko)

Bonjour et bienvenue sur le forum

Un essai à tester. Te convient-il ?

Bye !

Bonsoir, salut gmb !

Ce n'est qu'une variante dico, mais sans tableau...

Sub Numéroter()
    Dim d As Object, k, itm, n&, i&, j%, cd$
    Set d = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        n = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To n
            k = .Cells(i, 1): itm = ";" & Cells(i, 4)
            If d.exists(k) Then
                If InStr(d(k), itm) = 0 Then d(k) = d(k) & itm
            Else
                d(k) = itm
            End If
        Next i
        Application.ScreenUpdating = False
        For i = 2 To n
            k = .Cells(i, 1): cd = .Cells(i, 4): itm = Split(d(k), ";")
            For j = 1 To UBound(itm)
                If itm(j) = cd Then .Cells(i, 2) = k & j: Exit For
            Next j
        Next i
    End With
End Sub

Cordialement.


Bonjour,

merci beaucoup à gmb et MFerrand pour la réponse rapide et efficace !!! C'est exactement ce que je voulais.

Les deux réponses fonctionnent bien pour moi, celle proposée par MFerrand donne aussi un numéro à la toute première ligne (pomme1 dans le fichier exemple) (c'est de ma faute, je l'avais oublié dans ma colonne "résultat souhaité"). Dans la macro proposée par gmp, je crois comprendre que le compteur est censé démarrer avec 1

dico(tabloA(1, 1)) = 1
    tabloB(1, 1) = tabloA(1, 1) & 1

Pourquoi le numéro ne s'affiche pas à la première ligne ? (J'ai envie de comprendre pour pouvoir faire !)

Si vous passez encore par là et que vous êtes d'humeur didactique, pourriez vous me dire aussi pourquoi passer par un dico ? D'après ce que j'ai lu, l'avantage est la rapidité ? Est-ce qu'on l'utilise donc toujours avec de grandes quantité de données ?

Pour le reste, j'ai compris (à peu de choses près) comment fonctionne la macro de gmp, mais pas entièrement celle de MFerrand. Ce n'est pas grave, comme elle me fait le boulot. Mais j'aurais très envie de comprendre, j'aime écrire des macros en fait...

Donc, si MFerrand a envie d'expliquer/traduire le code dans les grandes lignes (ou des parties), ou qqn d'autre :

Que font exactement les deux boucles ?

J'ai exécuté le "pas à pas", mais il n'y a rien de "visible" me permettant de bien comprendre. J'ai cherché les mots clés, mais c'est la syntaxe qui me pose problème...

Donc, merci encore à gmp et MFerrand pour votre aide ! J'ai appris plein de choses.

Cordialement, gremlin

Bonjour,

D'abord, je n'ai pas compris ce que tu dis s'agissant de la numérotation du premier élément, tu numérotais bien pomme1 dans ton exemple ?

L'outil Dictionary génère une série d'éléments, chacun identifié par une clé (key) et accueillant une valeur (item). Il contitue un filtre naturel dans la mesure où il ne peut y avoir deux éléments avec la même clé.

Ainsi une boucle sur ta colonne A de ce type :

For i = 2 To n
    d(.Cells(i, 1).Value) = ""
Next i

où l'on parcourt la colonne en générant à chaque ligne un élément dico dont la clé est la valeur de la cellule (on n'y met aucun contenu particulier) produira à la fin un dictionnaire dont le nombre d'éléments sera égal au nombre de valeurs différente dans la colonne A. Il n'y aura aucun doublon : un seul élément pomme, un seul élément mangue, etc.

En outre l'outil est particulièrement rapide, ce qui n'est pas le moindre intérêt de son utilisation.

Je pensais que ma macro serait plus facile à comprendre que celle de gmb ! Ça n'a pas l'air d'être le cas pour toi !

Pourtant elle ne comporte que deux parties bien distinctes : une boucle de la ligne 2 à la fin où l'on construit le dictionnaire, une deuxième boucle identique où l'on affecte la colonne B.

Tu vas rapidement comprendre le mécanisme. On constitue d'abord le dico (exemple sur l'élément pomme) :

On trouve pomme en A2, en D2 : "aaa". On crée l'élément pomme avec comme contenu (une chaîne) : ;aaa

On retrouve pomme en A3, en D3 : "bbb". L'élément existe, on ne le crée plus, on modifie son contenu :

pomme => ;aaa;bbb

On continue ainsi jusqu'à la fin. Pour l'élément pomme, chaque fois qu'on le rencontre on ajoute à son contenu le contenu de la colonne D. Chaque valeur ajoutée étant séparée des autres par un point-virgule.

Avant d'ajouter une valeur au contenu, on teste si elle ne s'y trouve pas déjà. On ne l'ajoute que si elle n'y est pas.

A la fin :

pomme => ;aaa;bbb;ccc;eee;hhh;jjj

Note que l'on commence par un point-virgule. Si l'on utilise ce caractère comme séparateur pour tranformer le contenu de cet élément dico en tableau avec Split, on obtiendra un tableau de 7 éléments d'indices 0 à 6 :

indice 0 => (vide)

indice 1 => aaa

indice 2 => bbb

indice 3 => ccc

indice 4 => eee

indice 5 => hhh

indice 6 => jjj

Comme on numérote à partir des valeurs en D pour chaque valeur différente en A, tu noteras rapidement que pour pomme, les valeurs rencontrées en D, listées dans l'ordre de leur apparition (sans doublon) fournissent un numéro qui, lorsqu'on transforme la liste en tableau, correspond à leur indice du tableau.

Donc dans le 2e parcours de ta feuille, lorsque par exemple on va rencontrer pomme en A et ccc en D, on extrait le contenu de l'élément dico pomme sous forme de tableau qu'on parcourt de 1 à l'indice maximal, jusqu'à trouver ccc. On le trouve à l'indice 3, on affecte donc : pomme3 !

J'espère que ce sera plus clair ainsi !

Cordialement.

Aaaah, génial ! Merci ! C'est très gentil d'avoir pris le temps d'expliquer. Les fonctions dico et split sont encore toute nouvelles pour moi, la syntaxe de gmb correspond plus à ce que j'ai déjà vu et fait. Là avec la traduction je comprends et pourrais continuer à travailler avec.

Pour le souci de la numérotation de la première ligne, oui, j'ai revérifié, et j'avais bien mis pomme1 en fait. Toujours pas réussi de trouver pourquoi la macro de gmb n'affecte pas 1 à la première ligne, je continue à chercher.

Souhaitant un bon weekend, cordialement

gremlin

Bojour à tous

Tout d’abord, un grand bravo à MFerrand, tant pour sa proposition que pour ses explications.

Je vais tenter d’ expliquer à mon tour ce que j’ai fait mais j’ai des complexes car je ne saurais faire mieux…

Tout d’abord, j’ai décidé de travailler avec des variables tableaux. Cela permet à la macro d’aller beaucoup plus vite qu’en venant lire les données les unes après les autres sur la feuille de calcul

Comme la première donnée sera toujours le nom de ce qu’on a en A2, suivi d’un ‘’1’’ on peut mettre d’ores et déjà ce résutat dans la première ligne du tabloB (tableau résultat qui sera en colonne B)

Ensuite, on fait une boucle à partir de la 2° ligne du tabloA jusqu’à la dernière ligne : Ubound(tabloA,1)

Et on pose les conditions :

Si la ligne considérée en colonne A a la même valeur que la ligne précédente en colonne A ET si la ligne considérée en colonneD a la même valeur que la ligne précédente en colonne D,

ALORS :

o On envoie dans le dictionnaire la valeur de ce qu’on a sur la ligne en colonne A (clé), et on lui donne cette même valeur (item)

o On écrit dans un tablo provisoire la valeur qu’a le dico pour le nom de la colonne A

Si ‘une au moins des 2 conditions précédentes n’est pas remplie,

ALORS :

o On écrit dans un tablo provisoire la valeur qu’a le dico pour le nom de la colonne A incrémenté de 1

o On majore de 1 la valeur du dictionnaire pour la valeur de la colonne A de la ligne

Lorsque ces deux cas ont été traités, on peut écrire dans le tabloB la valeur qu’on aura à la ligne étudiée.

Quant à savoir pourquoi au final la ligne du premier résultat n’a pas de numéro à la suite du nom (pomme et non pomme1), il s’agit d’une bourde que j’ai commise en oubliant d’effacer une instruction inutile : la 2° avant la fin.

Ci-joint la version corrigée.

Bye !

Merci gmb ! Maintenant je peux de nouveau dormir tranquillement.

Vraiment contente d'avoir rejoint ce forum, j'apprécie énormément ! Tes explications sont très claires (pour moi en tout cas). J'espère que je pourrais bientôt rendre la monnaie et aider à mon tour sur ce forum.

Bonne semaine à tous,

gremlin

(qui s'en va allègrement numéroter des lignes)

Bonjour de nouveau !

Pour donner la suite de mes déboires et pour tous ceux qui seraient intéressés par ces fonctions :

Je me suis rendu compte d'une différence entre les codes de gmb et MFerrand qui pour moi est capitale. La macro de gmb assigne à chaque clé du dico la valeur de la clé même, de la même colonne,

donc pomme=>pomme. La macro de MFerrand assigne directement la valeur trouvé en colonne D (précédé de ";"), donc pomme=>;aaa. Gmb écrit la condition 2, à savoir valeur en D à la ligne i différente de D à la ligne i-1 comme condition 2 dans la boucle IF, alors que MFerrand l'intègre donc dans le dico (vous me corrigerez si malgré tout j'ai mal compris !)

A l'inspection de mes données, je me suis rendu compte d'un facteur que je n'avais pas mentionné (pq pas pensé aux effets possibles) : De fait, dans ma colonne D, il se peut que la même valeur (disons aaa) revienne plusieurs fois. La macro de MFerrand la traite alors comme une valeur déjà assignée à la clé (disons pomme), et lui affecte l'indice correspondant (ici donc 1) :

Colonne A Colonne D Résultat MFerrand

Pomme aaa Pomme1

Pomme bbb Pomme2

Pomme ccc Pomme3

Avocat ddd Avocat1

Avocat ddd Avocat1

Pomme aaa Pomme1

Or, ce n'est pas ce qu'il me faut en fait. Je ne l'avais pas précisé (j'écrirais toujours un pseudocode complet avant de commencer désormais !), mais seulement les répétitions adjacentes en D pour un même élément en A doivent recevoir le même numéro (cf. avocat dans le tableau ici). La dernière ligne de Pomme ici devrait être incrémenté et recevoir le résultat Pomme4.

Comme la macro de gmb a comme condition de la boucle IF la ligne suivante :

If tabloA(i, 1) = tabloA(i - 1, 1) And tabloD(i, 1) = tabloD(i - 1, 1) Then

çà prend bien en compte seulement les répétitions immédiatement adjacentes en D et ça marche parfaitement pour mes besoins.

J'ai essayé de modifier aussi le code de MFerrand, en changeant toute référence à la colonne D par la colonne A (surligné ci-dessous), mais ça ne fonctionne évidemment pas, chaque élément reçoit une seule valeur, i.e. soi-même, et l'indice est 1 partout. Est-ce que ce serait adaptable aussi pour la fonction Split dans la macro de MFerrand, ou ça ne marche pas du coup ?

Sub Numéroter()
        Dim d As Object, k, itm, n&, i&, j%, cd$
        Set d = CreateObject("Scripting.Dictionary")
        With ActiveSheet
            n = .Cells(.Rows.Count, 1).End(xlUp).Row
            For i = 2 To n
                k = .Cells(i, 1): itm = ";" & Cells(i, 1)
                If d.exists(k) Then
                    If InStr(d(k), itm) = 0 Then d(k) = d(k) & itm
                Else
                    d(k) = itm
                End If
            Next i
            Application.ScreenUpdating = False
            For i = 2 To n
                k = .Cells(i, 1): cd = .Cells(i, 1): itm = Split(d(k), ";")
                For j = 1 To UBound(itm)
                    If itm(j) = cd Then .Cells(i, 2) = k & j: Exit For
                Next j
            Next i
        End With
    End Sub

Bonne journée et bien cordialement

gremlin

Bonsoir,

J'ai effectivement conçu cette méthode pour éliminer tout doublon ! Tu n'as sans doute pas été assez précise sur ce point, j'ai relu et c'est ambigu, et il n'y avait pas d'exemple de doublon conservé dans le modèle. Mais si tu as une méthode qui répond à tes spécifications, inutile de chercher à modifier mon code... il faut changer la méthode de constitution du dico en n'éliminant que les doublons qui se suivent, mais ensuite il faut tester l'environnement... or l'intérêt de cette méthode était qu'une fois le dico constitué on n'avait plus à se préoccuper de l'environnement, ce qui permettait aussi le cas échéant de la finaliser sous forme de fonction...

Cordialement.

Rechercher des sujets similaires à "vba countif conditions multiples"