Fonction de recherche en VBA

Bonjour à tous,

Pour comprendre les explications que je vais vous donner, je mets mon fichier en pièce jointe.

Je souhaite faire une recherche d'une valeur dans un tableau, en utilisant le VBA.

Dans l'onglet "Base", on considère la base de données.

C'est dans l'onglet "Récap" que je veux travailler.

On retrouve les numéros matricules de l'onglet "Base", mais dans le désordre. Dans cet exemple, je souhaite récupérer les performances de chacun qui se trouve dans l'onglet "Base", les copier, puis les coller en face des numéros matricules correspondant de l'onglet "Récap". Il est très simple de faire ces actions avec une fonction RECHERCHEV, mais je ne veux volontairement pas l'utiliser. Je souhaite le faire avec une macro en VBA. (en effet, il s'agit là d'un exemple représentatif de mon vrai fichier que je ne peux malheureusement pas vous dévoiler)

Ce que je pense faire, c'est de copier dans le presse-papier le numéro matricule situé en A2 (onglet "Récap"), aller sur l'onglet "Base", faire une recherche avec un Ctrl+F, coller le presse-papier, puis valider. Ensuite je veux que la performance correspondante (certainement en se décalant en relatif avec un ActiveCell.Offset) soit copiée, puis collée en B2 de l'onglet "Récap".

Une fois que cette action est faite pour la première ligne, passer à la ligne suivante. En fait, reboucler ces actions tant qu'il y a des valeurs dans la colonne A. (J'imagine utiliser une boucle du type Do...While, ou autre dans ce genre)

Les tableaux n'ont pas de tailles prédéfinies, il peuvent faire 30 lignes comme 5000, voire plus.

Comme vous pourrez le remarquer, j'ai utilisé une "mise en forme de tableau", ce qui à l'avantage d'avoir une mise en forme automatique quand on rajoute des lignes par exemple.

Et ensuite, je souhaite faire la même chose mais dans le cas où les 2 onglets seraient dans des fichiers différents.

Je vous remercie déjà d'avoir pris le temps de lire toute cette demande, et aussi pour l'aide que vous pourrez m'apporter.

Bonjour,

Il est certain que ton opération peut être faite par formules... Selon le volume on peut vouloir éviter les formules... mais si tu veux faire du VBA, abandonne tout de suite ton idée baroque pour programmer vraiment en VBA : soit éviter tout copier-coller (lent), de travailler avec les éléments actifs (inefficace, c'est l'enregistreur qui fait ça !), et opérer globalement avec des tableaux et utiliser les caractéristiques des tableaux Excel qui évitent de faire référence aux feuilles... VBA ça ne s'imagine pas, ça s'apprend !

Sub Perf()
    Dim Tbl, i&, d As Object
    Set d = CreateObject("Scripting.Dictionary")
    Tbl = [Tableau1].Value
    For i = 1 To UBound(Tbl)
        d(Tbl(i, 3)) = Tbl(i, 5)
    Next i
    If d.Count = 0 Then Exit Sub
    Tbl = [Tableau3].Value
    For i = 1 To UBound(Tbl)
        Tbl(i, 2) = d(Tbl(i, 1))
    Next i
    [Tableau3].Value = Tbl
End Sub

Cordialement.

bonjour

salut au passage MFerrand

sans formule ni VBA : un simple TCD (eh oui, les TCD servent aussi sans avoir aucun nombre à afficher ! )

voir aussi ce que donne la solution quand un même matricule aura vendu 2 articles et plus.

amitiés à vous et à tous

Merci beaucoup pour vos réponses.

La macro semble fonctionner comme je le souhaite. J'essaierai de l'appliquer à mon vrai tableau au boulot, mais il n'y a pas de raison pour qu'elle ne fonctionne pas.

Par contre, serait-il possible de m'expliquer le fonctionnement de cette macro en détail ? Je suis très curieux et j'aimerais pouvoir en refaire une similaire de façon autonome.

Merci

Bonsoir à tous,

Dans la seconde boucle, attention toutefois à l'instruction d'affectation si celle-ci n'est pas accompagnée de la méthode exists.

Tbl(i, 2) = d(Tbl(i, 1)) crée implicitement une clé si celle-ci n'existe pas

C'est comme si on écrivait : d(Tbl(i, 1)) = Empty

For i = 1 To UBound(Tbl)
    Tbl(i, 2) = d(Tbl(i, 1)) <------ ici
Next i

il vaut mieux l'écrire comme ceci :

For i = 1 To UBound(Tbl)
    If d.exists(Tbl(i, 1)) Then
        Tbl(i, 2) = d(Tbl(i, 1))
    End If
Next i

Dans le cas exposé, aucune conséquence facheuse mais méfiance quand même

En résumé, attention au contexte.

Cétait simplement pour saluer MFerrand :

klin89

Hello Klin89 !

J'ai en effet suivi le même raisonnement que toi ! et éliminé le test étant assuré que j'affectais alors la valeur Empty.

Cordialement.

Bonjour binome18 !

serait-il possible de m'expliquer le fonctionnement de cette macro en détail ?

Voilà les éléments sur lesquels elle repose et que tu pourras facilement utiliser :

1) Les tableaux Excel :

Tableau1 et Tableau3 sont des noms de tableaux Excel, insérés automatiquement par Excel. Ces noms désignent la zone de données des tableaux concernés (ligne d'en-tête exclue) et sont uniques dans le classeur, ils permettent donc un accès direct (sans avoir à invoquer la feuille qui les contient) aux plages qu'ils désignent.

NB- La notation entre crochets (appelée notation compacte) est équivalente à des expressions telles que Range("Tableau1")...

2) Utilisation de tableaux (VBA) :

Une expression telle : Tbl = [Tableau1].Value constitue l'affectation d'une plage (plus précisément des valeurs de la plage) à une variable de type Variant, ce qui produit un tableau à 2 dimensions (respectivement lignes et colonnes de la plage origine) de base 1. On parcourt donc aisément les lignes d'un tel tableau résultant de 1 à UBound(Tbl) [ou UBound(Tbl, 1)] et les colonnes de 1 à UBound(Tbl, 2). Ces parcours sur tableaux VBA sont plus rapides que les mêmes sur les plage d'origine.

On affecte la plage de données du 1er tableau (Excel) à Tbl. Puis après traitement (que l'on verra ci-dessous), la variable redevenant disponible on lui affecte alors la plage de données du 2e tableau (Excel). On opère les ajout dans le tableau Tbl, qu'on réaffecte ensuite à la plage origine, qui se trouve ainsi mise à jour.

Ces méthodes d'affectations directes de valeurs, de plage à plage, de plage à tableau, de tableau à plage... sont propres à VBA et se substituent avec profit aux méthodes de copier-coller utilisées dans Excel, car beaucoup plus rapides.

3) Dictionnaire :

On utilise un dictionnaire pour traiter la Base. Un dictionnaire est une collection d'éléments identifiés par une clé (unique, donc n'admettant pas de doublon) auxquels on peut affecter une valeur ou item. En traitant le premier tableau Tbl, on constitue un dictionnaire d'éléments Matricule (uniques), contenant une valeur Performance.

En traitant le tableau cible, on appelle l'élément dictionnaire correspondant au matricule traité du tableau afin d'ajouter au tableau la valeur performance recueillie dans l'élément dico.

Dans le cas où le matricule n'existait pas dans la base, on génère un élément dico vide et aucune valeur ne sera alors affectée à la cible (voir explications de Klin89).

Cordialement.

Merci beaucoup pour ces explication, c'est maintenant plus clair pour moi. Malgré tout, je vous demande encore une petite explication à propos du code VBA.

Sub Perf()
...
        d(Tbl(i, 3)) = Tbl(i, 5)
...
        Tbl(i, 2) = d(Tbl(i, 1))

End Sub

Que représentent les chiffres 3 et 5, ainsi que 2 et 1 dans les instructions ci-dessus ?

Merci

Ce sont les numéros de colonnes de tableaux...

Merci.

Je vais maintenance essayé d'adapter cette macro à mon tableau final.

Il est possible que je vous repose quelques questions, ne soyez pas surpris.

J'ai appliqué cette macro à mon tableau final.

Curieusement, il y a certaines valeurs pour lesquelles tout fonctionne bien, et d'autres ou ça ne fonctionne pas.

Comme si les valeurs n'étaient pas trouvées dans le tableau de départ, alors qu'elles y sont !!

J'ai inspecté les formats de cellule au cas où, mais je n'ai pas vu d'anomalie.

Auriez-vous une idée du problème ?

Malheureusement, je ne peux pas joindre mon fichier avec les données professionnelles.

Merci

Bonjour,

Les formats n'ont aucune incidence, le code travaille sur des tableaux de valeurs.

Au demeurant, toutes les données figurant dans le classeur modèle fourni sont des données de type String, soit du texte pour lequel le format est en outre indifférent.

Evidemment, si tu travailles en fait sur des données d'autres types, c'est que ton modèle n'était pas conforme à tes données originales, et si le résultat n'est pas au rendez-vous, c'est peut-être que tes données diffèrent de données texte.

Cordialement.

J'ai réussi à remédier au problème en exécutant ma macro 2 fois. Je ne comprends pas pourquoi, mais ça fonctionne.

J'aimerais maintenant aller un peu plus loin, en considérant que les 2 onglets sont dans des fichiers Excel différents.

Donc, l'onglet "Base" dans le fichier n°1, et l'onglet "Récap" dans le fichier n°2.

Que faut-il alors modifier dans la macro ?

Merci

re

salut au passage MFerrand.

la macro fonctionne mais tu ne sais pas pourquoi ? pas glop

tu n'as pas dit ce qui t'empêche de faire un simple TCD (mon message plus haut) ?

à te relire

En fait le fichier qui contient la base de données restera plus ou moins figé. Quelques lignes pourrons éventuellement s'ajouter dans le tableau.

Le deuxième fichier sera une extraction d'une nomenclature, et dans celui-ci, il faut venir chercher les infos du premier fichier de base de données.

Si j'enregistre une macro dans un classeur de macros personnelles, je peux exécuter ma macro depuis n'importe quel fichier Excel. Alors qu'un TCD ????

D'après ce que je crois comprendre, le TCD est forcément lié à un fichier particulier et ne fonctionne que depuis ce fichier. A la différence de la macro, qui peut s’exécuter depuis tous les fichiers Excel à condition qu'elle soit enregistrée dans le classeur de macros personnelles.

Est-ce que je fais erreur ?

Merci

re

créer un fichier avec un TCD basé sur un fichier de données, puis menu TCD, Changer la source

amitiés

Donc, si je comprend bien...

Quand je ferai une nouvelle extraction d'une nomenclature, il faudra que j'aille sur le fichier sur lequel est basé le TCD, puis aller changer la source pour choisir le fichier d'extraction.

Ai-je bien compris ?