XL 2019 Recherche horizontale avec VBA

[Amilo]

Bonjour à tous,

Je dois faire une recherche entre 2 onglets et présenter le résultat horizontalement avec en titre de colonnes le libellé correspondant (voir fichier).

Pour information, malgré un gros nettoyage, j’ai un fichier d’origine très lourd (4000 ko en xslx et 1500 ko en xlsb).
Pourtant, les quantités de données dans le classeur ne justifient pas d’après moi, une taille aussi élevée (respectivement 9000 lignes et 15 000 lignes env. avec une dizaine de colonnes pour chacun des 2 onglets).

Après tentative avec Power Query et Power Pivot d'une recherche horizontale, j’ai le fichier qui bloque soit :
- sur un message « Mémoire Insuffisante… »,
- met un temps extrêmement long et j’arrête la requête au bout de 20 min (pourtant une petite requête de 3 ou 4 étapes avec Power query)

Inutile donc que j’essaye avec une formule mais j’aimerais tester avec VBA

Pouvez-vous svp m’aider pour un essai VBA.

Le résultat souhaité se trouve dans le 1er l’onglet « Inv Site » en colonne F:I
La recherche se fait selon les données du 2ème onglet.

Merci d’avance pour votre aide

Cordialement

 

[Dranreb]

Bonjour.
Un essai.

 

[Amilo]

Bonjour Dranreb 😉,

Merci beaucoup pour votre proposition,
Je vais essayer d'adapter le code sur le fichier d'origine de mon collègue, je pense plutôt en fin de journée.

Je vous tiendrai informé

Merci encore, à bientôt

Bonne journée

 

[mapomme]

Bonjour @Amilo 🙂 , @Dranreb 🙂,

Une version de ma pomme à la sauce mapomme.

 

[Amilo]

Bonjour @mapomme,

Merci beaucoup pour cette version à la sauce mapomme 😀,
Pour l'instant, je n'ai que survolé le code à Dranreb et le vôtre mais pas encore testé sur le fichier réel.

Celui de Dranreb comporte plusieurs modules dont un module de classe (je suis plutôt débutant pour comprendre tout cela),

Sinon, l'adaptation ou modification principale, sera surtout de décaler le résultat sur la cellule Z3 au lieu de F3 actuellement

Je me suis rapidement penché sur le fichier exemple mais sans y parvenir avec le code à Dranreb.
A défaut d'y arriver, j'adapterai le fichier réel au code pour tester.

Avec votre code, j'ai mis 2 min à trouver les variables à modifier sur le fichier exemple. Je pense pouvoir l'adapter sur le réel

Je vous tiens au courant après essai de vos 2 versions.

Merci à vous

Bonne fin de journée

 

[Dranreb]

Un module de classe c'est comme le plan d'une maison: ça permet d'en construire, et donc par la suite d'en visiter, de les occuper et d'en démolir.
Là vous n'avez pas à vous en soucier: c'est ma fonction Gigogne qui les construit et les range dans une collection qu'elle renvoie. Il y a juste à savoir qu'ils ont deux propriétés, Id et Co et ce qu'elle y met dans chacune. J'ai un classeur xlsm précurseur d'un xlam muni d'une page d'aide à l'utilisation si ça vous intéresse.

 

[mapomme]

Re @Amilo,

Attention !

Par fainéantise et parce que j'étais convaincu que la feuille"Résultat" était bidon, je suis allé à la facilité. Cela se traduit par un effacement complet de la feuille résultat avant d'y coller les résultats (instruction .Columns.Clear).

Il faut donc bien adapter la partie effacement et affichage à votre cas et d'abord travailler sur une copie de votre fichier. Ne nous faites jamais confiance 😛.

 

[Amilo]

Re, @Dranreb , @mapomme ,

J'ai commencé à tester les 2 codes et le point commun c'est que j'ai d'emblée le même message d'erreur "Type défini par l'utilisateur non défini"

- J'ai essayé de voir si un autre type proche à "New Dictionary" figure ou non parmi la liste (voir capture)
- J'ai vérifié dans les références s'il fallait cocher une case ou non mais en vain.

Je ne sais donc pas comment résoudre ce problème.

@Dranreb, merci pour vos explications concernant le module de classe, en fait j'avais lu il y a presque 10 ans un livre entièrement dédié à VBA.
J'avais déjà rencontré cette notion mais j'avoue que je suis resté encore au stade de débutant côté VBA 😛
Pour le classeur muni d'une page d'aide à l'utilisation, sincèrement je ne pense pas pouvoir arriver un jour à utiliser ou comprendre "Gigogne"...., VBA est malheureusement au delà de mes compétences !
Par contre, heureux de savoir que je n'ai pas à me soucier de ce module pour mes éventuelles adaptations.
Cependant, si vous pouviez svp m'indiquer comment avoir le résultat en cellule Z3 au lieu de F3 ça m'arrangerait beaucoup 🙂

@mapomme, merci d'avoir évoqué l'instruction Columns.Clear,
Même si je fais confiance aux membres avec une "notoriété" certaine, je fais toujours une copie du fichier avant les essais 🙂,

Par contre, est-ce que je peux désactiver (placer en commentaire) voire supprimer cette ligne ?
Comme vous vous en êtes douté, il y a d'autres données sur cette feuille

Merci d'avance

Cordialement

 

[mapomme]

Re @Amilo

méthode 1 :
Essayez, pour mon code, de modifier la ligne :

Dim derlig&, t, dcol As New Dictionary, i&, dlig, j&, n&, x

Par :

Dim derlig&, t, dcol, i&, dlig, j&, n&, x

méthode 2 (qui pourra sans doute aussi fonctionner pour @Dranreb ) :

• aller dans VBE (environnement de développement pour VBA)
• puis cliquer sur le menu Outils / Références...
• vérifier que la référence "Microsoft Scripting Runtime" est cochée. Sinon cocher la.
• cliquer sur OK
• (en fait, le type Dictionary est défini dans la bibliothèque "Microsoft Scripting Runtime")

 

[mapomme]

Par contre, est-ce que je peux désactiver (placer en commentaire) voire supprimer cette ligne ?
Comme vous vous en êtes douté, il y a d'autres données sur cette feuille

Certainement. L'une ou l'autre solution fera l'affaire.

Suivant la présentation de votre feuille de résultat, le transfert des résultats risque d'être plus ou moins compliqué. Il faut effacer les précédents résultats sans risquer de toucher au reste. Si vous avez des difficultés, alors il nous faudra votre feuille réelle (avec des données bidon !) pour vous aider.

 

[Dranreb]

si vous pouviez svp m'indiquer comment avoir le résultat en cellule Z3 au lieu de F3 ça m'arrangerait beaucoup

Dans le classeur que j'ai joint il n'est pas en F3 mais en C3. D'ailleurs je l'ai envoyé dans un ListObject, alors sa position je m'en fiche. Mais vous pouvez le déplacer manuellement ailleurs si vous voulez, ça suivra. C'est toujours tout à la fin que ça se passe avec mes code, le déchargement du tableau dynamique TRés vers une plage. C'est dans les toutes dernières instructions.

 

[Amilo]

Re,

@mapomme , je viens de comprendre mon erreur 🙂,

En fait, c'était la 1ère chose que j'avais faite pour la méthode 1, à savoir supprimer le type derrière la variable dcol
Comme cela ne fonctionnait pas, comme dit, je suis allé dans les références à la recherche d'une case à cocher en comparant les 2 fichiers.
Je suis passé donc à côté de cette case, et j'ignorais qu'une fois cochée, la référence disparait de l'ordre alphabétique pour remonter en haut de la liste.

Résultat des tests : un peu moins de 5 min de temps d'attente env., ce qui est convenable comparé à un test power query : je pense que PQ ne doit pas aimer des caractéristiques du fichier qui m'échappent surtout que je n'utilise jamais ce fichier au travail.

Seule optimisation si je peux me permettre, pouvez-vous svp intégrer une ligne dans le code afin d'ajouter la colonne "N°Série" à gauche du tableau (voir colonne en vert dans le fichier ci-dessous)

@Dranreb , merci encore une fois,
Résultat des tests : impressionnant la rapidité, il me semble en moins de 20 sec,
J'ai vérifié 2 ou 3 lignes et cela semble également donner le résultat escompté.

Sinon, si je peux me permettre également, pourrais-je svp avoir une version en ajoutant la colonne "N° Série" à gauche du tableau mais en cellule Z3,

Et également obtenir un tableau sans numéroter les titres de colonnes si ce n'est pas trop de travail.
En fait, les 2 m'intéressent mais je ne sais pas encore le choix qui sera fait.

Je ne suis pas certain de pouvoir réussir seul.

Merci d'avance à vous

Bonne fin de soirée

 

[Amilo]

@Dranreb , nos messages se sont croisés,

Exact, le tableau commence en C3 dans mon fichier réel,
Je regarderai pour modifier l'emplacement
Par contre, j'aurais du mal pour ajouter la colonne "N° Série" évoquée dans mon précédent message

Merci d'avance

 

[Dranreb]

Pourquoi aurez vous du mal avec la colonne "N° Série" ?
Ma dernière version élimine les numéros sur les Statuts sans doublon. Ce n'est pas possible dans un tableau Excel d'avoir des titres de colonnes en double.

 

[Amilo]

Désolé Dranreb, j'ai dû quitter le PC, je regarderai votre fichier demain,

Sinon, il est vrai qu'il n'est pas possible d'avoir des noms identiques dans un tableau structurés mais rien n'empêche de mettre le même titre dans une plage. Peut-être que votre tableau est structuré de la sorte.

Je vous ferai un retour sinon demain

Bonne nuit