Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

[Frk222]

Bonjour à tous,

J'utilise donc Excel 2007 sur Windows XP (SP3), et je cherche à mettre en place un tri sur une page différente de la source de ce tri, en fonction de critères présent à la fois sur plusieurs lignes et sur plusieurs colonnes.

Sur ma page sources, j'ai donc un tableau de 10 colonnes et d'un nombre de lignes variables, je souhaite récupérer des infos pour chaque ligne sur lesquelles la colonne des quantité est supérieure ou égale à 1 sur la page Source. Une ligne sera créée sur la page destination à chaque fois que, dans les colonnes G, H, I et J de la page Source il y aura une valeur (peu importe la valeur) en reprenant des infos présente dans les différentes cases de la dite ligne, et ce pour chaque valeur trouvée (une valeur dans G, H et I me donne 3 lignes sur la page destination, une valeur dans H seulement me donne une ligne sur la page destination). Une fois la ligne traité, la prochaine ligne créée sur la page Destination le sera d'après la prochaine ligne qui aura sa quantité supérieure ou égale à 1 et qui aura des valeurs dans G, H, I ou J.


Pour résumer je dois :

- Rechercher dans la colonne quantité la prochaine ligne non nulle (à priori, une fonction RechercheV devrait faire l'affaire)
- Si je trouve une ligne non nulle, créer une ou plusieurs lignes sur la page destination, en fonction des valeurs contenues dans G, H, I, J.
- Importer les infos de la ligne de la page source sur la ou les ligne de la page destination.
- Passer aux lignes suivantes sans tenir compte les lignes vide (où la quantité est nulle) de la page source pour incrémenter mon tableau de la page destination dès que je trouverai une quantité non nulle, de la même façon que décrit plus haut.

J'ai donc joint un document exemple, avec une page source telle que je l'utilise en général, et un page destination à construire (ça n'est bien entendu pas à vous de le faire, mais n'ayant pas ou peu de base pour travailler, je n'ai pas su aller plus loin, je l'ai donc mise en l'état avec des commentaires pour comprendre la fonction de chaque colonne).

J'espère que mes explications ne sont pas trop évasive et que je trouverai des réponses et des solutions pour mettre ce tableau en œuvre, je suivrais bien entendu ce fil de très près et suis prêt à répondre à toutes demandes d'explications et/ou précisions la dessus.

Merci à vous, Frk.

 

[Excel-lent]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Bonjour et bienvenu sur le forum Frk222,

Tu as mis la bonne pièce jointe? Car celle ci :
-> possédent des données personnels (or comme indiqué dans Lien supprimé, mieux vaut dépersonnaliser ton fichier pour éviter de laisser des données confidentiels/sensibles)
-> les pages (onglets/feuilles) "source" et "destination" ne figurent pas dans ton fichier

Peux-tu enlever tous les onglets inutile à l'explication du problème (et à sa résolution) et rajouter les fameuses pages dont tu parle dans ton explication?

A première vu, en lisant tes explications une partie sera réalisable via la fonction "RECHERCHEV" et/ou "INDEX" / "EQUIV", mais pour le reste, à priori, il va falloir passer par le VBA.

A bientôt
Cordialement

 

[Frk222]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

En effet, une sauvegarde n'était pas passé, du coup il restait pas mal de choses à enlever, merci à toi.

J'ai lu pas mal de chose sur Excel labo sur les fonction INDEX et EQUIV, mais j'avoue ne pas tout saisir, pour le moment je bloque sur la fonction qui me permettrait de créer des lignes en fonction du résultat d'une cellule sur une autre feuille.

Si je trouve comment faire ça, je saurais alors isoler les lignes dont j'ai besoin dans un tableau intermédiaire, pour les ré-isoler ensuite dans un 2ème tableau, à partir duquel je pourrais élaborer mon tableau fianl, ça sera long, pas forcément optimal et compliqué, mais fonctionnel pour moi.

Enfin bref, j'ai corrigé le fichier, si tu veux y rejeter un œil.

 

[Excel-lent]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Hello,

En effet, étant donné ton tableau et tes besoins il va falloir passer par le VBA.

Si personne n'est passé par là d'ici ce soir je me pencherais sur ton problème et t'enverrais ma réponse (car là je dois m'absenter).

Bonne après midi

 

[Frk222]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Arf, j'ai des bases très limité en VBA, je connais quelques truc, je m'étais "amuser" un peu avec, mais par manque de temps et de connaissances, je n'ai jamais pu réellement apprendre le langage et comment l'utiliser correctement.

Enfin, si tu peux me donner des pistes, ça sera déjà ça et je t'en serait reconnaissant .

 

[Excel-lent]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Bonjour Frk222,

Tu voulais mettre les mains dans le camboui. Je t'en donne l'occasion

Tu trouveras ci-joint la permière moitiée de la macro. Comme tu pourras le voir, j'ai détaillé au maximum ce que j'avais fais et ce qu'il te reste à faire.

Pour résumé, tu n'as plus qu'une variable à rajouter : "NuméroLigneTableauDestination"

Dans cette variable tu stockeras le numéro de la nouvelle ligne, de l'onglet "Destination", où tu colleras la ligne copié sur l'onglet "Source".

Donc comme tu l'as vu, au début de la macro il faudra la déclarer. Bref, dire à Excel que cette variable contiendra un nombre entier et compris entre 1 et 65 536 (dans le cas où tu utilise Excel 2003). Si tu utilse Excel 2007, se sera entre 1 et 1 048 576 (car Excel 2007 contient non pas 65 536 lignes mais 1 048 576).

Ensuite tu peux attaquer la macro. Cette dernière devra :
-> copier la ligne ... (ou seulement les cellules... de cette fameuse ligne) ... (chiffre contenu dans la variable "NuméroLigne") de l'onglet "source"
-> coller cette ligne sur l'onglet "Destination", sur la ligne ... (chiffre contenu dans la variable "NuméroLigneTableauDestination")
-> répéter l'opération autant de fois que le chiffre indiqué dans la variable "NbLigneAcréer"
-> ne pas oublier à incrémenter (via une boucle ou manuellement) la variable "NuméroLigneTableauDestination".

Ensuite, le restant de la macro déjà écrite permettra de répéter cette même opération pour toutes les autres lignes de l'onglet "source".

Si tu coince sur un point, n'hésite pas, il y aura toujours quelqu'un pour te remettre sur la voie

Bon courage

 

[Frk222]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Super .

On a trouvé une solution secondaire (en gros, un 2ème tableau qui reprend TOUTES les lignes, qu'on a déja pré-rempli, et qu'on filtre sur la quantité, et qui nous donne un tableau "destination" qui présente bien, mais qui n'est en rien automatisé, c'est juste de l’esbroufe, ça marche mais ça va être un calvaire à mettre en place ;D), mais pour ma culture personnel et par curiosité, je vais tacher de terminer et de rendre fonctionnelle cette macro .

Avant d'y mettre les doigts, j'aimerai déjà bien comprendre tout ce que tu m'as inscrit, donc la déclaration, ça je pige (dis moi juste à quoi correspondent "Integer" et "Byte" (je sais que c'est le "type" de la variable, mais je ne connais pas vraiment leur différentes fonctions, et ce qu'on peut faire ou ne pas faire avec tel ou tel type)).

Ensuite, la variable "NbLigneAcréer", qu'est-ce qui me la défini dans ta macro? Quand je l'ouvre, je vois :

NbLigneAcréer = 4 - Application.WorksheetFunction.CountBlank(Range("G" & NuméroLigne & ":J" & NuméroLigne))

Donc si je me trompe pas, tu demandes une soustraction entre 4 et le nombre de blanc contenu dans les colonnes G,H,I et J, le résultat étant enregistré en tant que "Byte", s'il est différent de 0, alors l'opération de création aura lieu?

Si ce que je viens de dire est bon, c'est que mes souvenirs de programmation en Pascal du lycée reviennent un peu , donc si j'ai bien saisi, il me reste à définir la destination des lignes créées (donc ici la première ligne vide du tableau "destination", qui je pense sera une variable a déclarer aussi en début de macro (il doit bien y avoir une fonction pour trouver la première case blanche sur une colonne)), et définir ce que je vais y mettre (copier la colonne "D" & "NuméroLigne" dans la case "C" & "NuméroLigneDestination" par exemple).

Jusqu'ici, ça te parait être dans le vrai?

 

[Excel-lent]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Bonsoir Frk222,

Je vais essayer de répondre à tes questions dans l'ordre pour être sûr de ne rien oublier :

dis moi juste à quoi correspondent "Integer" et "Byte"

Pas la peine de se casser la tête à retenir chaque "type" de variable, suffit de mettre cette page en favoris.

Pour information, si dans ta variable tu met des nombres entiers compris entre 0 et 250, tu peux la définir en tant que :
-> "Byte", car d'après la définition indiqué sur le fameux lien : nombres entiers positifs compris entre 0 et 255
-> "Integer", car c'est pour stocker des nombres entiers compris entre -32 768 et 32 767
-> "Long", car c'est pour stocker des nombre entier, dont la valeur est comprise entre -2 147 483 648 et 2 147 483 647
-> "Currency", car ...
-> ...

Bref, tous sont juste. Mais il vaut mieux prendre celui qui correspond le mieux : "Byte", car ta macro utilisera moins d'espace mémoire (seulement 1 octet, alors que la variable "currency" en utilise 8) et donc tournera plus vite.

Dans ta macro, la variable NbLigneAcréer contiendra un nombre entier compris entre 0 et 4

la variable "NbLigneAcréer", qu'est-ce qui me la défini dans ta macro

Tu m'as dit qu'il fallait copier :
-> 1 fois la ligne ... si la case G ou H ou I ou J contient quelque chose
-> 2 fois la ligne ... s'il y a deux cases de remplis (parmis les cases de la colonne G, H, I, J)
-> 3 fois la ligne ... s'il y a trois cases de remplis (parmis les cases de la colonne G, H, I, J)
-> 4 fois la ligne ... si les 4 cases sont remplis (parmis les cases de la colonne G, H, I, J)

= 4 - Application.WorksheetFunction.CountBlank(Range("G" & NuméroLigne & ":J" & NuméroLigne))

En language excel, ça correspond à la formule :
= 4 - NB.VIDE(G34:J34)

34 est un exemple. Dans ta macro, le chiffre 34 sera remplacé le chiffre 34 la première fois que la boucle s'exécute, puis 35 la seconde fois, puis ... jusqu'à balayer TOUTES les lignes de ton tableau.

Bref, si je fais "4 - nombre de case vide", j'obtiens en faite le nombre de cellule pleine , donc le nombre de fois qu'il faut recopier cette fameuse ligne.

Donnée qu'évidement j'enregistre précieusement dans une variable pour la réutiliser plus tard.

il me reste à définir la destination des lignes créées (donc ici la première ligne vide du tableau "destination"

Tout à fait.

qui je pense sera une variable a déclarer aussi en début de macro

Exactement. Donc comme il s'agira d'un numéro de ligne, se sera forcément un nombre entier. Donc pas la peine d'utiliser une variable de type : "Currency", "Single", "Double", "Date", "String", "Variant" mais plutôt : "byte", "integer" ou "long".

Dans ce cas, puisque tu ne peux pas savoir à l'avance la taille de ton tableau, par du principe : prévoir le pire. Ta version Excel compte sauf erreur : 1 048 576 lignes? Donc prévoit que ta variable contiendra un nombre entier entre 1 et 1 048 576. Donc tu choisi ...?

il doit bien y avoir une fonction pour trouver la première case blanche sur une colonne

Pas la peine de mettre une fonction complexe! Ton tableau commencera forcément juste en dessous de ta ligne de titre. Or tu sais déjà où est situé ta ligne de titre. Dans le fichier exemple, cf. onglet "Destination" : ligne 2.

Donc il te suffit d'écrire
MaVariable = 2

définir ce que je vais y mettre (copier la colonne "D" & "NuméroLigne" dans la case "C" & "NuméroLigneDestination" par exemple). Jusqu'ici, ça te parait être dans le vrai?

C'est exactement ça.

Tu aura donc un code dans ce genre :
Sheets("Destination").Range("C" & MaVariable) = Sheets("Source").Range("D" & NuméroLigne)
Sheets("Destination").Range("D" & MaVariable) = Sheets("Source").Range("ZA" & NuméroLigne)
Sheets("Destination").Range("E" & MaVariable) = Sheets("Source").Range("ZZ" & NuméroLigne)

Et à la fin : MaVariable = MaVariable + 1

Bonne continuation
A demain soir

 

[Frk222]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Re,

Merci pour toutes ces précisions !!

J'aimerai revenir sur un point :

Dans ce cas, puisque tu ne peux pas savoir à l'avance la taille de ton tableau, par du principe : prévoir le pire. Ta version Excel compte sauf erreur : 1 048 576 lignes? Donc prévoit que ta variable contiendra un nombre entier entre 1 et 1 048 576. Donc tu choisi ...?

il doit bien y avoir une fonction pour trouver la première case blanche sur une colonne
Pas la peine de mettre une fonction complexe! Ton tableau commencera forcément juste en dessous de ta ligne de titre. Or tu sais déjà où est situé ta ligne de titre. Dans le fichier exemple, cf. onglet "Destination" : ligne 2.

Donc il te suffit d'écrire
MaVariable = 2

Je ne suis pas d'accord avec ça, car je ne connais QUE le début du tableau, à savoir la ligne 2, mais ensuite, vu que le nombre de lignes créées, même sur la première ligne analysée par la macro est variable, je ne sais pas où en est mon tableau pour la seconde ligne analysée. Ou alors, je devrais récupérer ce que tu as appelé MaVariable, l'ajouter à NbLigneAcréer et la sauvegarder dans une nouvelle variable pour me donner la ligne de départ pour la copie suivante, ça me parait moins souple que de chercher directement avant chaque copie la première ligne de la colone A (par exemple) qui me renverrai "blank" pour définir la première ligne de destination pour l'analyse en cours.

Ceci dit, je me trompe peut être puisque si MaVariable+1 est fait à chaque fin de ligne, normalement ça doit aller MAIS pour une même ligne analysée, je dois copier parfois sur plusieurs lignes dans la destination, ce qui oblige à affiner la copie. Je vais y réfléchir un peu dans la journée pour trouver le "pas à pas" sans code, car en l'état, j'ai l'impression que même si on crée bien le bon nombre de lignes, je ne saurai pas copier correctement puis fermer la boucle sans rien oublier, il manque au moins une variable (celle qui défini le nombre de ligne restante à remplir pour la ligne analysée en cours) et surtout (car il te manque une info en fait, je viens de le voir) pour une même cellule de destination, j'ai plusieurs choix sur la ligne de la feuille source (les fameuse G,H,I et J), il manque donc une variable, à priori lié à la précédente (celle qui défini le nombre de ligne restante à remplir pour la ligne analysée en cours) qui me dis où j'en suis dans la copie (première ligne copié, j'importe G, deuxième j'importe H, etc, jusqu'à ne plus rien avoir à copier).

Bref, je synthétise tout ça dans la journée, encore merci pour ton aide, j'adorerai être capable de faire tout ça tout seul, c'est très ludique je trouve, mais apprendre le VB seul c'est l'enfer, bien dommage .

 

[Excel-lent]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Salut Frk222,

Je ne suis pas d'accord avec ça, car je ne connais QUE le début du tableau, à savoir la ligne 2, mais ensuite, vu que le nombre de lignes créées, même sur la première ligne analysée par la macro est variable, je ne sais pas où en est mon tableau pour la seconde ligne analysée.

Partir de tout en bas de ton tableau, à la recherche de la première ligne rempli (pour trouver la dernière ligne de ton tableau (onglet "Destination"), c'est très possible en effet, mais demandera plus de ressource à excel. Et inutile pour la raison suivante car tu vas forcément créer une boucle pour le copiage 1 à 4 fois ta ligne (car répétitif), donc tu ferras l'incrémentation de ta variable MaVariable à l'intérieur même de la boucle.

For i = 1 To NbLigneAcréer
' le fameux code décrit ci-dessus
Sheets("Destination").Range("C" & MaVariable) = Sheets("Source").Range("D" & NuméroLigne)
....

' C'est ici que tu incrémente MaVariable
MaVariable = MaVariable + 1

Next i

C'est vrai, je le reconnais, pour se remettre au VBA, tu n'as pas choisi la macro la plus simple! Tu vas devoir imbriquer plusieurs boucles l'une dans l'autre.

Le plus important c'est de garder une vue d'ensemble


----------------------------------------
Boucle 1 (déjà faite)
Balayer l'onglet "Source" ligne par ligne"

---Sous-boucle 2 (déja faite)
---Analyser l'onglet "Source", la colonne Qté
---Compter le nombre de fois qu'il faudra recopier la ligne

-------Si NbDeFoisArecopierLigne >= 1
-------va falloir retrousser les manches pour recopier cette ligne sur l'onglet lol

------------Sous-boucle 3 (à faire)
------------De i=1 à 4 fois (remplacer 4 fois par le fameux chiffre contenu dans la variable NbLigneAcréer) :
------------boucle For... To... Next

------------Fin De (= "Next i" en language VBA)
------------Fin sous-boucle 3
------------(Excel va retourner automatiquement à la boucle 1 et analyser la ligne suivante de l'onglet "Source"
------------et recommencer la macro)

-------Fin Si

---Fin sous-boucle 2

Fin Boucle 1
----------------------------------------

j'ai l'impression que même si on crée bien le bon nombre de lignes, je ne saurai pas copier correctement puis fermer la boucle sans rien oublier, il manque au moins une variable (celle qui défini le nombre de ligne restante à remplir pour la ligne analysée en cours)

Il s'agit de la fameuse variable ci-dessus "i"

Tu as très bien deviné

et surtout (car il te manque une info en fait, je viens de le voir) pour une même cellule de destination, j'ai plusieurs choix sur la ligne de la feuille source (les fameuse G,H,I et J), il manque donc une variable, à priori lié à la précédente (celle qui défini le nombre de ligne restante à remplir pour la ligne analysée en cours) qui me dis où j'en suis dans la copie

La fameuse boucle sous-boucle 3 For ... To ... Next
La variable i prendra tour à tour la valeur 1 puis 2 puis ... jusqu'à atteindre le nombre de fois qu'il faut copier la ligne ... de l'onglet "source".
Bref, excel sera parfaitement où il en est, le nombre de ligne déjà copier, le nombre de ligne restant à recopier.

(première ligne copié, j'importe G, deuxième j'importe H, etc, jusqu'à ne plus rien avoir à copier).

Pour cela, il te suffit d'insérer un sous boucle à l'intérieur qui détermine la colonne rempli (G, H, I, J) et en fonction, copier ce qu'il faut où il faut.

j'adorerai être capable de faire tout ça tout seul, c'est très ludique je trouve, mais apprendre le VB seul c'est l'enfer, bien dommage .

Il faut avouer que tu commence vraiment très fort! Pour commencer le VBA, généralement on commence par des macros de 5-8 lignes, sans boucle, sans variable, sans fonction complexe (nb.vide), sans avoir à gérer plusieurs feuilles, sans ...

Si tu n'avais pas déjà la logique "programmation", des notions en language VB/VBA, j'aurais pas donné cher de ta peau! Alors que là tu semble te débrouiller comme un chef.

Bon courage.

 

[Excel-lent]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Hello,

Très important, mais tu le sais déjà à mon avis, pour ton code :
-> n'hésite pas à aérer ton code (le saut de ligne ne change rien l'excécution de ton code mais le rend plus facile à écrire, comprendre, corriger)
-> n'hésite pas à faire des retraits de lignes pour bien dissocier chaque boucle. Très important pour s'y retrouver quand il y a plusieurs boucles insérées les unes dans les autres.

A te lire

Cordialement

 

[Frk222]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Héhé, je sais que si je m'y intéressai vraiment, je pourrais bien m'en sortir, mais j'ai pas le temps hélas . Je vais relire et revoir toutes les infos que tu me donne avec la macro sous les yeux et voir où j'en suis.

Sinon, je suis pas spécialement fan des GSXF, mais pour avoir eu un modèle "R" (1100 de 91, ceux qui vont bien ), les motorisations fin 80 début 90 étaient top la dessus, ce osnt de bonnes motos, parfaites pour tailler la route .

 

[Excel-lent]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Hello,

J'ai remplacé ma GSXF par une VFR, mais je n'ai pas encore eu le temps de faire un site sur ces motos. Qui sais, un jour je trouverais peu être le temps de faire ce site, ainsi qu'un autre sur les GSXR

Pour la macro, avec l'habitude tu sera capable de faire cette macro en 15mn maxi. Bref, rentable pour pouvoir les développer pendant ton temps de travail.

L'an dernier j'ai mis 2 jours pour faire un fichier (pendant mon temps de travail) : macros, UserForm, ... mais ce fichier :
-> me fait gagner facilement 2H00 à chaque utilisation
-> réduit à zéro les risque d'erreurs (erreur de copier/coller, faute d'étourderie, oubli, ...)
-> fait des taches annexes très utiles mais que je n'avais pas le temps de faire avant et qui me simplifie la vie sur d'autres taches

Temps amorti en 4 mois, or j'utilise ce fichier depuis déjà 3 ans

Bref, parfois il faut savoir perdre du temps, pour en gagner par la suite

Mais c'est clair, autant tu peux faire des macros professionnels durant ton temps de travail, autant apprendre le VBA au boulot c'est moins top!

V.

 

[Frk222]

Re : Rechercher des valeurs sur plusieurs lignes puis plusieurs colones.

Tu veux pas l'adresse mail de mon boss pour lui expliquer? J'ai l'impression que quand ça vient de moi il pense que je me fout de sa gueule . Bref, tout à fait d'accord avec toi, surtout que ce fichier est utile à plusieurs personnes, plusieurs fois par jour, et que chaque ligne copier nous fera économiser une feuille à photocopier, bref ...

Mais je pense qu'une fois en place, je me la réserverai ,car si je suis le seul à la comprendre, je serai aussi le seul qui devra la mettre en place, l'expliquer, la modifier, et je serais aussi responsable des éventuelles erreurs qu'elle pourrait engendrer, même si ce n'est pas ou plus moi qui l'utilise, donc, elle sera pour bibi .