Fonction de recherche à partir de deux données enchainées

Question

Merci de m'avoir accepté sur le forum et j'espère respecter au mieux les règles pour ce premier poste. Je travail actuellement sur la création d'une base de donnée pour le suivi de tâche sur des outillages dans mon entreprise. Je souhaite ajouter une information dans ma base existante (voir feuille "Suivi maint. Outil Met." du fichier joint) que je veux placer dans la colonne "Type de produit". L'information se trouve dans la deuxième feuille du fichier joint (voir feuille "Extract-Avis") qui est une extraction en masse de l'ERP (SAP) de l'entreprise des avis de maintenance. Mon besoin est de trouver la valeur "type de produit" dans la feuille "Extract-Avis" à parti de deux informations : - le numéro d'avi qui se trouve dans la colonne "Avis N°" de la première feuille du fichier - et le numéro de repère qui se trouve dans la colonne "Repère de la première feuille du fichier Ces deux informations que j'utilise dans la recherche se retrouvent dans la feuille "Extract-Avis" dans les colonnes A et E ("Avis" et "Texte"). Je ne peux pas utiliser la fonction RECHERCHEV car un numéro d'avis contient plusieurs numéro de repère et je suis obligé d'associer ces deux numéros pour que ma recherche soie exhaustive. Or RECHERCHEV ne fait sa recherche que sur une valeur. Je propose donc de créer la fonction suivante : "TypeProduit" Elle prend comme argument mon numéro d'avis et mon numéro de repère (mes deux données de recherche) et renvoie la valeur de la colonne D "TextCodePartObj" de la feuille "Extract-Avis" correspondante . L'idée c'est de faire deux RECHERCHEV mais imbriquées : sur la feuille "Extract-Avis" je fais un premier filtre sur la colonne A avec ma valeur de numéro d'avis puis un deuxième filtre sur la colonne E avec ma valeur de repère. Problème : Elle fonctionne si je l'exécute pas à pas avec le mode débug mais j'ai une erreur de référence circulaire quand je l'utilise directement dans une cellule et je ne trouve pas de solution ... Merci par avance pour vos idées, je ne suis peut-être pas partie sur la bonne piste en créant une fonction ... je suis ouvert à toute autre proposition Voici le code de ma fonction : Function TypeProduit(Avis As Range, Repere As Range) As String Dim NCAvis As String 'Définition valeur du Numéro d'avis NCAvis = Avis.Value Dim NCRepere As String 'Définition valeur du Numéro de repère NCRepere = "*" &amp; Repere.Value &amp; "*" Dim LigneCellTypeProduit As Integer 'Définition Ligne sur la quelle coller la valeur de type Produit trouvée LigneCellTypeProduit = Avis.Row 'Supprime les filtres de la feuille ExtractAvis avant de commencer la recherche With Worksheets("Extract-Avis") If .FilterMode = True Then .ShowAllData Application.Wait Now + TimeValue("0:00:02") End With 'Va selectionner la valeur de Avis dans la feuille "ExtractAvis" à l'aide d'un filtre. Sheets("Extract-Avis").Select Range("$A$1:$L$58244").Select Sheets("Extract-Avis").Range("$A$1:$L$58244").AutoFilter Field:=1, Criteria1:=NCAvis 'Va selectionner la valeur de repère dans la feuille "ExtractAvis" à l'aide d'un filtre. Sheets("Extract-Avis").Range("$A$1:$L$58244").AutoFilter Field:=5, Criteria1:=NCRepere 'Recupérer le contenu de la première cellule résultant des filtres précedents Selection.End(xlDown).Select TypeProduit = Cells(ActiveCell.Row, "D") Sheets("Suivi maint. Outil Met.").Select Cells(LigneCellTypeProduit, 7) = TypeProduit End Function...

MFerrand · Answer

Je n'ai pas encore regardé ta fonction perso, je comptais commencer par un résultat formule normalement assez simple dans cette situation... Mal m'en a pris Un vrai cauchemar ton fichier ! Des cellules au format Texte ! Des espaces en début de chaînes Des espaces intermédaires non réguliers Des valeurs qui se convertissent en nombre mais pas toutes dans les mêmes cas... Un vrai cumul : =INDEX('Extract-Avis'!$D$1:$D$11;SOMMEPROD(LIGNE('Extract-Avis'!$D$2:$D$11)*(CNUM('Extract-Avis'!$A$2:$A$11)=CNUM(E5))*(CNUM(GAUCHE(SUPPRESPACE(SUBSTITUE('Extract-Avis'!$E$2:$E$11;"rep";""));3))=CNUM(F5)))) Ce qui finit par donner cette formule : conversion en nombre de tout pour être sûr d'aligner les types de données, suppression d'espaces... mais comme je suis passé par une phase de repassage de toutes les cellules en format standard avec recopiage en valeurs &#91;en effet, la particularité d'une cellule au format Texte est que le format ne s'applique pas au contenu de la cellule s'il y en a un, mais ne prendra effet que lors des affectations ultérieures de valeur, et vice-versa pour en sortir, d'où la galère que cela représente, et le conseil qu'on donne habituellement de ne jamais utiliser volontairement ce format et d'opérer si possible des conversions dès le départ sur des données importées qui parfois arrivent avec&#93;, je ne saurais garantir qu'elle marche dans tous les cas qui pourront être rencontrés. C'était pour indiquer qu'il y avait moyen d'opérer avec les ressources existantes en formulation d'Excel, ce qui est fait. Mais là, j'incline à penser qu'une fonction personnalisée risque fort d'être préférable, surtout si l'on parvient avec à éviter les obstacles... Pour le moment : pause et je regarderai ton code (si personne ne l'a encore fait) Cordialement...

MFerrand · Answer

Juste un premier coup d'oeil...

Outre que j'y vois pas moins de 4 Select (que je ne manquerai pas de supprimer), des déclarations et affectations entrelacées... peut-être quelques autres petits trucs qui vont m'irriter dans la mesure où je suis partisan d'un respect plutôt strict de règles d'écriture, non pas parce que je fais une fixation [même si c'est parfois le cas ] mais parce que c'est un confort inestimable quand on doit lire du code... Comme tout un chacun dans le même cas je déroge aux règles "classiques" quand ça me convient mieux, et en contrepartie je trouve acceptable de considérer "bien écrit" un code qui déroge aussi mais pas sur les mêmes points que moi et qui offre la même régularité ; dans un tel cas, je suis en visite et me plie aux usages locaux (mais il faut qu'il y ait des usages !)...

Bref !! avec mes excuses pour cette digression que je pourrait poursuivre longtemps (et qui est inachevée d'ailleurs ), j'en viens à la question clé (après premier survol rapide) :

Il y a un choix à faire : soit une fonction personnalisée à utiliser dans une formule (comme les autres), soit une macro qui traite ponctuellement ton fichier pour y insérer la donnée type de produit manquante.

Chaque solution a ses avantages et ses inconvénients et je n'ai pas d'avis pour pousser dans un sens ou l'autre, mais il me semble que tu es à cheval entre les deux, et à cheval sans être sûr de ce qu'on va enfoucher c'est casse...

Il faut bien distinguer les deux types de procédures les plus courants : Sub et Function.

Sub fait... et ne renvoie généralement rien.

Function peut faire ou ne pas faire mais renvoie toujours un résultat !

C'est ce qui permet de faire des fonctions personnalisées qui fonctionneront dans les même conditions que les fonctions intégrées d'Excel.

Si le calcul justifie une fonction, ce que je vais regarder de plus près, cette fonction doit renvoyer le résultat.

Si on estime qu'il vaut mieux traiter le fichier pour le mettre à jour... on fait une procédure en conséquence.

Ce qui n'est pas incompatible, uen Sub peut défiler les lignes de la base pour affecter la valeur de la données recherchées en faisant appel à une fonction qui lui fournit le résultat.

A+

MFerrand · Answer

A la réflexion, il valait mieux partir dans une nouvelle direction...

Fonction personnalisée :

Function TPROD(Avis, Rep, Ext As Range)
    Dim d As Object, temp, i&
    Application.Volatile
    Set d = CreateObject("scripting.dictionary")
    With Ext
        For i = 1 To .Rows.Count
            temp = Val(.Cells(i, 1)) & "rep" _
             & Val(Right(.Cells(i, 5), Len(.Cells(i, 5)) - 3))
            d(temp) = .Cells(i, 4)
        Next i
    End With
    temp = Val(Avis) & "rep" & Val(Rep)
    TPROD = d(temp)
End Function

Elle est formalisée pour être utilisée comme fonction de feuille de calcul dans une formule.

Mais rien n'empêche de l'utiliser dans une procédure affectant directement la valeur qu'elle renvoie...

En espérant que ton fichier réel n'ait pas de caractéristiques qui auraient échappé au modèle.

Cordialement.

11gregruynat-suivi-outil-metal-forum.xlsm (22.82 Ko)

MFerrand · Answer

Il faudrait un échantillon de 3 à 4000 éléments, qu'on puisse tester dans des conditions plus réalistes.

edit : Comment as-tu utilisé la fonction ? As-tu mis ton tableau en 3e argument de la fonction, les 2 premiers étant le couple Avis et Repère cherché.

J'ai considéré qu'il n'y avait pas possibilité de doublon pouvant renvoyer des valeurs différentes dans ta base, c'est à dire qu'un couple Avis-Repère ne pouvait correspondre qu'à un seul type de produit.

Un renvoi d'erreur peut provenir d'un couple non trouvé dans la base : ça on peut introduire un test pour lui faire renvoyer N/A! ou rien plutôt qu'une autre erreur.

Egalement si dans la base des rep sont précédés d'espaces : ça on peut le rectifier par un Trim(.Cells(i, 5) avant d'ôter les 3 premiers caractères.

Ou bien si le repère ne permet de retrouver la valeur numérique en ôtant 3 caractères.

MFerrand · Answer

Inutile de filtrer, une fonction prend en considération la totalité de la plage qu'on lui soumet, que ce soit masqué ou non. Sauf une partie des fonctions SOUS.TOTAL et AGREGAT conçues pour s'adapter aux filtrages...

En l'occurrence, ici la fonction concatène toute la base avant de renvoyer un résultat : en utilisant dictionnary chaque élément produit est affecté comme valeur d'un élément dictionnaire dont la clé est constituée par la concaténation qu'on a utilisé, chaque clé est unique, il suffit donc à la fin d'appeler la clé pour avoir la valeur cherchée.