Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2019 Recherche horizontale avec VBA

Amilo

XLDnaute Accro
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
 

Pièces jointes

  • Invent.xlsx
    10 KB · Affichages: 20
Dernière édition:

Amilo

XLDnaute Accro
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
 

Amilo

XLDnaute Accro
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

XLDnaute Barbatruc
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.
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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

XLDnaute Accro
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
 

Pièces jointes

  • Message d'erreur.jpg
    136.6 KB · Affichages: 17
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Re @Amilo

méthode 1 :
Essayez, pour mon code, de modifier la ligne :
VB:
Dim derlig&, t, dcol As New Dictionary, i&, dlig, j&, n&, x
Par :
VB:
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")
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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

XLDnaute Barbatruc
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

XLDnaute Accro
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
 

Pièces jointes

  • Invent.xlsx
    10.2 KB · Affichages: 5
Dernière édition:

Amilo

XLDnaute Accro
@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

XLDnaute Barbatruc
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.
 

Pièces jointes

  • GigogneAmilo.xlsm
    66.8 KB · Affichages: 5

Amilo

XLDnaute Accro
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
 
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…