boucles imbriquées pour suppression de lignes selon 2 critères

julien974

XLDnaute Occasionnel
Bonjour,

Je suis en stage et j’aurais besoin d’aide concernant la suppression de lignes selon des critères.

Mon fichier est composé de différentes en-tête de colonne (destinataire, type, poste, lieu stock, co, OF, prèl)

Je souhaiterais supprimer certaines lignes de la manière suivante :

Supprimer les lignes dont destinataire = 23 et type = SO46D et poste = P4 et lieu stock = BLE

Si et seulement si il existe une ligne dont destinataire = 23 et type = SO46D et poste = P7 et lieu stock = BLE

ET dont les N° CO sont identiques !

Sinon passer à la ligne suivante



Voici un début d’idée mais je ne m’en sort pas, il manque la seconde condition qu’il faudrait imbriquer, mais comment ? :


Sub supprligneseloncritères()

Sheets("BLEU2").Activate
Range("A2").Select
While ActiveCell <> ""
If ActiveCell.Value = "23" And ActiveCell.Offset(0, 1).Value = "SO46D" And ActiveCell.Offset(0, 2).Value = "P4" And ActiveCell.Offset(0, 3).Value = "BLE" Then
Selection.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Wend

End sub

Ci-joint, le fichier (sachant qu’il comptabilisera beaucoup plus de lignes…)

Merci de votre aide,

Julien974
 

Pièces jointes

  • simulation supp ligne.xls
    23 KB · Affichages: 82
G

Guest

Guest
Re : boucles imbriquées pour suppression de lignes selon 2 critères

voici une solution,

Dans la macro modifiée du fichier joint

' Stocker une formule qui compte le nombre de ligne répondant à la deuxième condition( présence de P7) dans une feuille annexe

Code:
Sheets("Feuil3").Range("A1").Formula = "=SUMPRODUCT((Feuil1!B2:B10=""SO46D"")*(Feuil1!C2:C10=""P7"")*(Feuil1!D2:D10=""BLE""))"

Ensuite :
Code:
If Sheets("Feuil3").Range("A1") > 0 Then
  
' on supprime les lignes si la valeur de Feuil3!A1 >0
  
End if

Laissons faire à Excel ce qu'il sait si bien faire (SommeProd)

Bon Travail
 

Cousinhub

XLDnaute Barbatruc
Inactif
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Bonsoir,
bonsoir Hasco

une autre proposition :
Tout d'abord, on fait un filtre élaboré avec les critères que tu veux dans la feuille 2.
On fait le filtre élaboré sur place dans la feuille 1, puis on scanne sur les cellules visibles si on obtient les deux conditions :

Poste = P4 et CO "x" identiques à Poste = P7 et même CO

regarde le fichier joint, et reviens si problème
Nota, les deux zones sont nommées dans le code, la zone "base" englobant tes données de la feuille1 et la zone "crit" les critères dans la feuille2
 

Pièces jointes

  • simulation supp lignev1.zip
    13 KB · Affichages: 33

julien974

XLDnaute Occasionnel
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Bonsoir,

Je ne pense pas que cela fonctionne car la ligne 8 contenant "P4" du fichier original à été supprimée alors qu'elle n'as pas de ligne homologue contenant "P7" et ayant le même numéro CO.

Par contre la ligne 2 à été convenablement supprimée.

Une boucle serait sûrement la bienvenue...

Qui veut bien tenter de m'aider?

Merci

Julien974
 

Kotov

XLDnaute Impliqué
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Bonsoir Julien, hasco, BhBh,

Si j'ai bien compris :
On vérifie d'abord s'il existe au moins une ligne comportant les valeurs suivantes :
Dest = 23 + Type = SO46 + Poste = P7 + Lieu = BLE

S'il existe au moins une ligne avec toutes ces données alors on supprime toutes les lignes du tableau qui présentent les valeurs suivantes :
Dest = 23 + Type = SO46 + Poste = P4 + Lieu = BLE uniquement si le n°CO est identique au n°Co de la première condition

Si c'est bien ta demande, je te propose la macro suivante :

Code:
Sub SupSI()
Dim NoCO()
Dim r&
Dim n&
Dim x&
Dim rr&

n = Cells(65536, 1).End(3).Row

ReDim NoCO(n - 1)
For r = 2 To n
    NoCO(r - 2) = Cells(r, 5).Value
    If Cells(r, 1) = 23 And Cells(r, 2) = "SO46D" And Cells(r, 3) = "P7" And Cells(r, 4) = "BLE" Then
        For x = 0 To r - 3
            If NoCO(r - 2) = NoCO(x) Then
                For rr = n To 2 Step -1
                    If Cells(rr, 1) = 23 And Cells(rr, 2) = "SO46D" And Cells(rr, 3) = "P4" And Cells(rr, 4) = "BLE" Then
                        Rows(rr).EntireRow.Delete
                    End If
                Next
                Exit Sub
            End If
        Next
    End If
Next r
End Sub

A priori, ça fonctionne.
Si c'est le code dont tu as besoin, je reste à ta disposition pour t'expliquer (si besoin) chaque ligne de la macro.

Bonne soirée
Kotov
 

Cousinhub

XLDnaute Barbatruc
Inactif
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Re-,
Attention, dans mon fichier, j'avais modifié tes données afin d'avoir plusieurs lignes à supprimer, donc si la ligne 8 a été supprimée, c'est que j'avais modifié tes données.
J'ai fait l'essai avec ton fichier originel, et il n'y a que la ligne 2 de supprimée
 

Kotov

XLDnaute Impliqué
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Re,

Après vérif, j'avais oublié une condition dans ma macro précédente.

Voici la correction (condition ajoutée en rouge):
Code:
Sub SupSI()
Dim NoCO()
Dim r&
Dim n&
Dim x&
Dim rr&

n = Cells(65536, 1).End(3).Row

ReDim NoCO(n - 1)
For r = 2 To n
    NoCO(r - 2) = Cells(r, 5).Value
    If Cells(r, 1) = 23 And Cells(r, 2) = "SO46D" And Cells(r, 3) = "P7" And Cells(r, 4) = "BLE" Then
        For x = 0 To r - 3
            If NoCO(r - 2) = NoCO(x) Then
                For rr = n To 2 Step -1
                    If Cells(rr, 1) = 23 And Cells(rr, 2) = "SO46D" And Cells(rr, 3) = "P4" And Cells(rr, 4) = "BLE" [color=red]And Cells(rr, 5) = NoCO(r - 2) [/color]Then
                        Rows(rr).EntireRow.Delete
                    End If
                Next
                Exit Sub
            End If
        Next
    End If
Next r
End Sub

Bonne soirée

Kotov
 

julien974

XLDnaute Occasionnel
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Re,

merci pour ces deux aides bhbh et kotov, je vais essayer de comprendre leur structure (ms si vs avez un peu de temps... je veux bien que vous m'aidiez. hihi)

Bref, cela fait quelques jours que je commence à programmer en VBA. C'est pas très simple pour les personnes qui commencent tout juste.

petite question! Vos formules fonctionnent-t-elles dans dans une base de 5000 lignes avec des destinataires, types, lieu stock,... différents?

Pour tout vous dire, j'effectue un stage en logistique industrielle. Mon objectif est d'étudier les flux de matière et d'optimiser les livraisons entre une plate forme logistique et des ateliers de montage.

Pour ce faire, je dois prendre la base de donnée de commande matérialisée par des lignes. Plusieurs lignes représentent un numéro de prélèvement. Chaque numéro de prélèvement correspond à un contenant (bac à roulette), sauf dans certains cas ou des numéros de prélèvement sont associés dans un même contenant. D'où ma volonté d'effacer certaines lignes par rapport au critère(s) cité(s) dans le premier message. Ces critère sont issus des modes de préparation de commande. J'ai donc à effectuer un multitude de suppression de ligne pour chaque atelier destinataire.

Ma démarche informatique est la suivante :

- Coller l'extraction d'un WMS sur excel (4000 à 5000 lignes de commande par jour)

- Dispatcher les lignes selon les zones de stockage (6 au total)

- Création d'un filtre élaboré sans doublon (un numéro de prélèvement = un contenant)

- création sur chaque zone de stockage, d'une macro qui supprime les lignes ayant un numéro de poste incorporé à un ou d'autres sur le même contenant (ce sur quoi je bloque, merci pour votre aide). Pour être plus clair, un contenant peut contenir pour un destinataire des marchandises pour les poste P4, P7 et P9. mais ceux ci doivent avoir le même n° CO. donc c'est pour cette raison que je supprime P4 et P7 par exemple, afin qu'il ne me reste plus qu'une seule ligne équivalente à un contenant! subtil mais efficace!

- Une fois les suppressions de ligne effectuées selon les différents critères de chaque zone de préparation, je synthétise le tout dans un tableau récapitulatif.


L'objectif est d'anticiper le nombre de contenant, afin d'optimiser, de ventiler et de fluidifier les flux de matières...

Dans un second temps, je devrais créer dans ce même programme une application évolutive pour modifier les critères automatiquement et insérer des nouveaux types.

Si vous avez des remarques, n'hésitez pas à m'en faire part,

Cordialement,

Julien974
 

julien974

XLDnaute Occasionnel
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Merci Kotov!

Ta formule à l'air de fonctionner.

Je veux bien que tu me l'expliques afin que je puisse la réutiliser avec d'autres critères.

Peux tu m'en simuler une avec deux Postes à supprimer?


Merci beaucoup pour ton implication,

Julien974
 

Kotov

XLDnaute Impliqué
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Re Julien,

La macro que je t'ai proposé fonctionne parfaitement pour le cas précis que tu as mis à notre disposition.
Pas de problème pour le nombre de lignes puisque la ligne suivante trouve la dernière cellule utilisée dans ta colonne A :
n = Cells(65536, 1).End(3).Row
C'est valable pour 10, 5000 ou 65536 lignes (la limite dans les versions d'Excel antérieures à 2007).
Donc pas de problème pour la quantité.

Toutefois, il va falloir faire des aménagements pour les autres recherches : au vu de ton fichier, j'ai en effet rédigé mon code en "dur" (ex : If Cells(r, 1) = 23 And Cells(r, 2) = "SO46D" And Cells(rr, 3) = "P4"). Il est possible de faire la même chose avec des variables pré-définies.
exemple : If Cells(r, 1) = vDest And Cells(r, 2) = vType And Cells(rr, 3) = vPoste.... il suffit ensuite de faire varier la valeur des variables pour que la macro s'adapte à toutes les recherches : si tu donnes successivement les valeurs 23, 35, 543 à vDest, et "P5", "P8", "P10" à vPoste, la macro fonctionnera pour chaque valeur.

Par ailleurs, ma macro fonctionne de la manière suivante :
Je déclare un tableau qui collecte le N°CO en même temps que la macro contrôle ta 1ère condition.
Dès que la 1ère condition est vérifiée, la macro passe en revue toutes les lignes et supprime la totalité des lignes qui vérifient la 2ème condition + n°CO identique en partant du bas vers le haut.

En clair : dès que la condition 1 est trouvée, toutes les conditions 2 sont supprimées.
A noter que le Exit Sub arrête aussitôt la macro pour éviter de boucler inutilement sur le reste du tableau.


Pour faire plusieurs recherches automatiques, il faut supprimer le Exit Sub et prévoir un GoTo Etiquette qui relance la macro sur une Etiquette placée sur la 1ère ligne active (juste avant n = Cells(65536, 1).End(3).Row)

Bon, ne connaissant pas tes connaissances en VBA, je crains d'être peu explicite.
Pour ce soir, je vais arrêter là, sachant que je me lève tôt demain et que le temps me manque pour te proposer rapidement un développement plus conséquent.

Demain, si je trouve un créneau, j'essaierai de te commenter chaque ligne de ma macro de départ, mais je n'aurai pas le temps de développer un programme complet vers lequel tu semble te diriger.

Bonne nuit
Kotov
 

Kotov

XLDnaute Impliqué
Re : boucles imbriquées pour suppression de lignes selon 2 critères

Bonjour Julien, le forum

J'ai modifié ma macro selon tes besoins.
Pour t'aider à la comprendre, j'ai commenté chaque ligne ( commentaire en bleu débute par '-->)
Je te joins également ton fichier avec les modifications que j'ai apporté (vois l'intérêt d'avoir mis en place une feuille "Paramètres")
Pour modifier les comparaisons, il te suffit de modifier les données à comparer sans avoir à toucher la macro.

Code:
Sub SupSICommentée()
[color=blue]
'--> Déclaration de 4 variables pour les différentes boucles
'--> A noter que r& est l'abréviation de Dim r as Long (entier long),
'--> les autres abréviations : % =As Integer, $ = As String, ! = As Single, # = As Double, @ = As Currency[/color]
Dim r&
Dim n&
Dim x&
Dim rr&
[color=blue]
'--> Déclaration de variables qui mémoriseront les valeurs à comparer
'--> A noter que je préfère signaler mes variables avec un petit "v" devant le nom pour éviter les conflits :
'--> en effet, Type étant un mot réservé par VBA, il n'est pas possible d'appeler ses propres variables avec un nom réservé.[/color]
Dim vDest&
Dim vType$
Dim vPoste1$
Dim vPoste2$
Dim vLieu$
[color=blue]
'--> je préfère déclarer la dernière variable en Variant (tous types) au cas où les valeurs à comparer pourraient être très disparates[/color]
Dim N°Co As Variant
[color=blue]
'--> Attribution des "valeurs-cibles" aux variables
'--> Notes que j'ai 2 variables pour comparer les Postes
'--> pour éviter de modifier la macro quand tu souhaites varier les valeurs,les valeurs sont inscrites dans la page "Paramètres"
'--> Elles sont donc modifiables à volonté[/color]
With Sheets("Paramètres")
    vDest = .Cells(1, 2)
    vType = .Cells(2, 2)
    vPoste1 = .Cells(3, 2)
    vPoste2 = .Cells(4, 2)
    vLieu = .Cells(5, 2)
End With [color=blue]
'--> Retour sur la feuille de données[/color]
Sheets("Data").Activate[color=blue]
'-->  n = la dernière cellule non vide de la colonne 1 (.End(3) = .End(xlUp))
'--> en clair, la macro part de la dernière cellule de la colonne A et remonte jusqu'à la 1ère cellule non vide[/color]
n = Cells(65536, 1).End(3).Row

[color=blue]
'--> Boucle : de la première rangée (r = 1) à la dernière rangée non vide (r = n)[/color]
For r = 1 To n
[color=blue]
'--> chaque cellule discriminante est comparée aux variables de la 1ère condition à vérifier [/color]
    If Cells(r, 1) = vDest And Cells(r, 2) = vType And Cells(r, 3) = vPoste1 And Cells(r, 4) = vLieu Then [color=blue]
    '--> si la comparaison A est vérifiée, N°Co prend la valeur cible qui servira dans la deuxième comparaison[/color]
    N°Co = Cells(r, 5)[color=blue]
        '--> je boucle sur l'ensemble du tableau[/color]
        For x = 1 To n [color=blue]
            '--> je m'assure que x est différent de r pour éviter de supprimer la ligne de comparaison à conserver [/color]
            If x <> r Then[color=blue]
            '--> s'il existe au moins une valeur de la 5ème colonne identique à la variable N°Co, alors on contrôle la 2ème condition.[/color]
            If N°Co = Cells(x, 5).Value Then[color=blue]
                '--> je lance une nouvelle boucle mais cette fois en partant du bas du tableau vers le haut pour la raison suivante :
                '--> quand on supprime la ligne en remontant, on ne modifie pas le n° des lignes qu'on va passer en revue ensuite
                '--> si tu supprimes les lignes en descendant, tu vas forcement modifier le N° des lignes que tu étudies ensuite [/color]
                For rr = n To 1 Step -1
                   [color=blue] '--> Vérification de la condition 2[/color]
                    If Cells(rr, 1) = vDest And Cells(rr, 2) = vType And Cells(rr, 3) = vPoste2 And Cells(rr, 4) = vLieu And Cells(rr, 5) = N°Co Then [color=blue]
                        '--> la condition est vérifiée, on supprime la ligne[/color]
                        Rows(rr).EntireRow.Delete
                    End If [color=blue]'--> condition 2 non vérifiée[/color]
                Next
            End If [color=blue]'--> pas de doublons de N°Co, inutile donc de tester la condition 2[/color]
            End If [color=blue]'--> x = r, inutile de supprimer la ligne servant de base à la condition 1[/color]
        Next[color=blue] '--> condition 1 non vérifiée[/color]
    End If
Next r
End Sub

En espérant t'avoir aidé.

Bonne soirée
Kotov
 

Pièces jointes

  • SupLigneKotov.xls
    44.5 KB · Affichages: 73
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
314 590
Messages
2 111 003
Membres
111 003
dernier inscrit
Youss77!!