Macro VBA : Trouver texte dans chaine de texte et renvoyer sa reference

Bonjour !

J'espere que vous allez bien !

Je suis en train d'essayer de faire fonctionné une macro a plusieurs conditions mais une des conditions me pose probleme .. Il s'agit, dans la macro, ci dessous du premiere "Else If".

ElseIf Lease_Code = "L00" Or Lease_Code = "L99" Or Lease_Code = "" Then

Cells(ligne, 6) = Application.VLookup("*" & Klaant_Name_1 & "*", Listing_2, 2, If_Close)

Pour vous expliquer, dans une feuille de calcul "Clients PCDO", Klaant_Name_1 fait référence a un texte representant le nom d'un client remplie manuellement et non standardisé (C'est à dire que par exemple le client "Orange" peut s'appeler, dans la colonne comprenant le Klaant_Name_1, "Sosh Mobile", "Orange France" ou encore "France Telecom Region Ouest". Dans une autre feuille de calcul (A savoir celle nommée "Reference Table PCDO" dans la macro), j'ai un tableau reprenant le nom voulu de mes clients et une partie du nom pouvant apparaitre dans la feuille de calcul "Clients PCDO". Ex du tableau dans "Reference Table PCDO".

Le but de ma macro est que VBA, cherche, dans ma boucle, si le nom Klaant_Name_1 contient un des "nom client" de ma "Reference Table PCDO" et me renvois le nom générique. Par exemple si mon "Klaant_Name_1" est "France Telecom Region Ouest", alors la macro identifie le "France Telecom" dans le texte et me renvois "Orange".

Autre chose, si aucune des conditions n'est remplie, je souhaite que ca me renvois "OTHERS", or, ici, la macro me renvois #N/A. Je ne comprends pas sachant que j'ai bien ajouté le "Else" a la fin.

PS : J'y arrive tres bien avec une formule Excel mais ma feuille "Clients PCDO" fait 100 000 lignes et Excel n'a pas la capacité de calculer ces 100 000 lignes, il crashe toujours avant.

J'espere que quelqu'un pourrait m'aider, je suis bloqué depuis plusieurs heures deja.

Sub Lease_Company_Name()
Dim Lease_Code As Variant
Dim Customer_Code As Variant
Dim Klaant_Name_1 As String
Dim Listing As Range
Dim Listing_2 As Range
Dim If_Close As Boolean

For ligne = 3 To 100000
Lease_Code = Cells(ligne, 4)
Customer_Code = Cells(ligne, 5)
Klaant_Name_1 = Cells(ligne, 26)
Set Listing = Sheets("Reference Table PCDO").Range("N:Q")
Set Listing_2 = Sheets("Reference Table PCDO").Range("O:P")
If_Close = False
If Lease_Code = "" And Klaant_Name_1 = "" Then
Cells(ligne, 6) = "OTHERS"
ElseIf Lease_Code = "L00" Or Lease_Code = "L99" Or Lease_Code = "" Then
Cells(ligne, 6) = Application.VLookup("*" & Klaant_Name_1 & "*", Listing_2, 2, If_Close)
ElseIf Lease_Code <> "L00" And Lease_Code <> "L99" And Lease_Code <> "" Then
Cells(ligne, 6) = Application.VLookup(Lease_Code, Listing, 3, If_Close)
Else
Cells(ligne, 6) = "OTHERS"
End If
Next ligne
End Sub

Edit modo : code mis en forme correctement, attention !

Bonjour,

Peux-tu préciser la nature du problème rencontré, la macro plante ? tu n'obtiens pas la bonne valeur ? le test n'est pas vérifié alors que tu penses qu'il devrait l'être ? autre ?

Bonjour Cylfo,

Merci de t'interesser a mon probleme haha.

Quand j'essaye en gardant les "*"&, la macro ne se lance pas car ca met une erreur sur la ligne de code. Autrement en les enlevant, la macro fonctionne mais ce n'est pas le resultat cherché .. Il cherche en effet seulement la valeur exacte donc ici, il me calculera correctement uniquement les valeurs ou il sera ecrit exactement dans Klaant_Name_1 "Orange", "Sosh" ou "France telecom".

En fait, je souhaiterai savoir si vous connaissez une formule VBA ou une combinaison de formules a mettre dans Lookup_Value pour qu'il cherche parmis le Klaant_Name_1 si il apparait "Orange", "Sosh" ou "France telecom" et qu'il me retourne le resultat correspondant donc ici "Orange".

Voici la formule Excel (qui fonctionne), ca peut peutetre aidé a comprendre :

=IFERROR(IF(OR($D2="",$Z2=""),"OTHERS",IF(OR($D2="L00",$D2="L99",$D2=""),VLOOKUP(INDEX('Reference Table PCDO'!$O:$O,MIN(IF(COUNTIF(Z2,"*" &'Reference Table PCDO'!$O:$O& "*")>0,ROW('Reference Table PCDO'!$O:$O),""))),'Reference Table PCDO'!$P:$Q,1,0),INDEX('Reference Table PCDO'!$P:$Q,MATCH(D2,'Reference Table PCDO'!$Q:$Q,0),1))),"OTHERS")

Bien a toi,

Il te met l'erreur 2042 ?

Si non, précises exactement quelle erreur tu as.

Mais si oui, c'est parce que VLOOKUP ne trouve pas de résultat. Pour éviter l'erreur, affecte la recherche à une variable de type variant et testes si la variable est de type erreur (vbError = 10) et suivant le cas ... Attention : le 2ème ElseIf pourrait produire le même effet ...

' Dans la partie déclaration
Dim vValVL

...
ElseIf Lease_Code = "L00" Or Lease_Code = "L99" Or Lease_Code = "" Then
    vValVL= Application.VLookup("*" & Klaant_Name_1 & "*", Listing_2, 2, If_Close)
    If VarType(vValVL) <> vbError Then
        Cells(ligne, 6) = vValVL
    'Else
    '   Cells(ligne, 6) = ??????
    End If
...

Tu peux aussi utiliser

Cells(ligne, 6) = Application.IfError(Application.VLookup("*" & Klaant_Name_1 & "*", Listing_2, 2, If_Close), "Valeur si non trouvé")

Plus concis ...

bonjour à tous,

tu peux aussi utiliser la méthode find de l'objet range. (code non testé)

Sub Lease_Company_Name()
    Dim Lease_Code As Variant
    Dim Customer_Code As Variant
    Dim Klaant_Name_1 As String
    Dim Listing As Range
    Dim Listing_2 As Range
    Dim re As Range
    dl = Sheets("Reference Table PCDO").Cells(Rows.Count, "N").End(xlUp).Row
    Set Listing = Sheets("Reference Table PCDO").Range("N1").Resize(dl, 4)
    For ligne = 3 To 100000
        Lease_Code = Cells(ligne, 4)
        Customer_Code = Cells(ligne, 5)
        Klaant_Name_1 = Cells(ligne, 26)
        Cells(ligne, 6) = "OTHERS" 'par défaut
        If Lease_Code = "L00" Or Lease_Code = "L99" Or Lease_Code = "" Then
            Set re = Listing.Columns(2).Find(klaant_name_1, lookat:=xlPart)
            If Not re Is Nothing Then
                Cells(ligne, 6) = re.Offset(0, 3)
            End If
        ElseIf Lease_Code <> "L00" And Lease_Code <> "L99" And Lease_Code <> "" Then
            Set re = Listing.Columns(1).Find(Lease_Code, lookat:=xlWhole)
            If Not re Is Nothing Then
                Cells(ligne, 6) = re.Offset(0, 3)
            End If
        End If
    Next ligne
End Sub

Bonjour tout le monde !

J'espere que vous avez tous passé un bon weekend ! Merci a tous pour vos nombreux retours ! Malheureusement j'ai l'impression qu'aucune de vos solutions ne résoud mon problème

@cyflo, maintenant mon code ne bloque plus et n'affiche plus de messages d'erreurs quand on le lance .. Cependant il ne trouve toujours pas la solution ...

@h2so4, j'ai testé la solution avec find et range, le code peut etre lancé mais idem il ne donne pas le bon resultat...

En fait dans vos 2 solutions, VBA trouve bien la réponse quand mon nom client est bien seul dans la chaine de mot (Si c'est écrit "Sosh" (et vraiment seulement "Sosh") dans la cell "Klaant_Name_1", VBA me retourne bien "Orange").

MAIS si il est écrit par exemple, dans la case "Klaant_Name_1", "Sosh Rennes Bretagne", VBA retourne #N/A ("OTHERS" en gros) au lieu de "Orange" (Alors qu'il est bien ecrit Sosh dans la chaine de texte). Pour palier a ca, j'avais éssayé d'ajouter "*"& dans le Vlookup pour lui signifier qu'il peut y avoir du texte avant et apres, mais ca ne marche pas.

Vous voyez ou je veux en venir ?

Non seulement le mot "Sosh" est référencé dans la colonne "P" de la feuille "Reference Table PCDO". Et ca suffit a Excel pour trouver le résultat voulu grace a cette formule : =IFERROR(IF(OR($D2="",$Z2=""),"OTHERS",IF(OR($D2="000",$D2="999",$D2=""),VLOOKUP(INDEX('Reference Table PCDO'!$O:$O,MIN(IF(COUNTIF(Z2,"*" &'Reference Table PCDO'!$O:$O& "*")>0,ROW('Reference Table PCDO'!$O:$O),""))),'Reference Table PCDO'!$O:$Q,2,0),INDEX('Reference Table PCDO'!$P:$Q,MATCH(D2,'Reference Table PCDO'!$Q:$Q,0),1))),"OTHERS")

Et en fait ce que je ne parviens pas a traduire en VBA c'est cette partie la : VLOOKUP(INDEX('Reference Table PCDO'!$O:$O,MIN(IF(COUNTIF(Z2,"*" &'Reference Table PCDO'!$O:$O& "*")>0,ROW('Reference Table PCDO'!$O:$O),""))),'Reference Table PCDO'!$O:$Q,2,0)

Ici le MIN(IF(COUNTIF(Z2,"*" &'Reference Table PCDO'!$O:$O& "*")>0 me renvoit le numéro de ligne pour lequel une partie de la chaine de texte klaant_name_1 match avec le nom du client

Aprés, si tu me dis que ce n'est pas traduisible en VBA, que cette logique ne peut pas fonctionné en VBA etc ... c'est que en effet il va falloir que je revois ma logique de recherche ... Et que je reparte de 0 haha

S.t.p. envois un extrait anonymisé de ton fichier car en recréer un pour reproduire ton contexte serait trop long et le risque qu'il soit "légèrement" différent trop grand.

=IFERROR(IF(OR($D2="",$Z2=""),"OTHERS",IF(OR($D2="000",$D2="999",$D2=""),VLOOKUP(INDEX('Reference Table PCDO'!$O:$O,MIN(IF(COUNTIF(Z2,"*" &'Reference Table PCDO'!$O:$O& "*")>0,ROW('Reference Table PCDO'!$O:$O),""))),'Reference Table PCDO'!$O:$Q,2,0),INDEX('Reference Table PCDO'!$P:$Q,MATCH(D2,'Reference Table PCDO'!$Q:$Q,0),1))),"OTHERS")

Dans la formule Exel que tu indiques (L00 et L99 sont devenus 000 et 999 ?), quelque chose m'échappe et je ne comprends pas qu'elle puisse donner le résultat attendu car sauf erreur possible de ma part :

• NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*") => NB.SI(plage; critère) or là, la valeur semble à la place de la plage et la valeur est la plage entourée du caractère jocker *. Donc à priori elle retourne systématiquement 1
• LIGNE('Reference Table PCDO'!$O:$O) => retourne systématiquement 1 car la référence de LIGNE() est une colonne
• SI(NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*")>0;LIGNE('Reference Table PCDO'!$O:$O);"") => SI(1>0; 1; "") = 1
• MIN(SI(NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*")>0;LIGNE('Reference Table PCDO'!$O:$O);"")) => donc MIN(1) = 1
• INDEX('Reference Table PCDO'!$O:$O;MIN(SI(NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*")>0;LIGNE('Reference Table PCDO'!$O:$O);""))) => INDEX('Reference Table PCDO'!$O:$O;1; "") = retourne à priori le titre de la colonne O
• RECHERCHEV(INDEX('Reference Table PCDO'!$O:$O;MIN(SI(NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*")>0;LIGNE('Reference Table PCDO'!$O:$O);"")));'Reference Table PCDO'!$O:$P;2;0) => RECHERCHEV(; 'Reference Table PCDO'!$O:$P;2; 0) = retourne le titre de la colonne P.

Oui pardon, j'ai 2 formules extremement proches, je t'ai copier coller la mauvaise, 000 est en fait L00 et 999 est en fait L99. ma faute.

Je t'ai mis en PJ le fichier vidé (les 100 premiéres lignes) de toutes les infos inutiles. j'ai nettoyé la feuille référence table donc la plage du tableau a légérement changé (Il passe de N:Q a F:I).

Je t'ai mis aussi le resultat de la foumule Excel des 100 premieres lignes (Colonne G, tu peux d'ailleurs retrouvé la formule en G2, le reste c'est un copier coller des valeurs pour alléger le fichier) pour que tu puisses voir quels resultats je cherche á obtenir en colonne F avec VBA. Tu verras, par ex, en ligne 44, 80 ou 99, le resultat de VBA et Excel ne colle pas.

Merci encore pour ton aide et le temps que tu me consacre !

Alors en fait, c'est un bon bricolage mais ca passe.

NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*") ca donne une matrice de la colonne G qui va noter TRUE ou FALSE a chaque fois quelle reconnaitre une chaine de caractere de Z2 dans la colonne O. Si tu mets cette fonction LIGNE apres ce NB.SI et que tu y mets en reference une colonne, la fonction LIGNE ne retournera non pas 1 mais le numéro de toutes les valeurs TRUE de la colonne de reference de NB.SI. Ensuite en liant ca avec MIN, ca ma donne le plus petit chiffre de la matrice (Qui est composé de numéro de ligne issue de notre fonction LIGNE), et donc le numero de ligne du premier TRUE de mon NB.SI. Puisque de ma colonne O il n'a y que des valeurs uniques, ca tombe toujours sur la bonne ref.

Apres tu as compris, Le resultat de MIN me donne le numero de ligne de mon INDEX.

Par ex, pour la ligne 44 le resultat de Vlookup c'est ca :

NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*") = TRUE pour toutes les lignes de mon tableau contenant ARVAL, BUSINESS, les 2 ou des vides.

LIGNE('Reference Table PCDO'!$O:$O) = Ca me rapporte tous les numeros de ligne de la colonne.

Maintenant si tu lies les 2 dans une formule, LIGNE ne retournera plus que les numeros des ligne TRUE de NB.SI.

SI(NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*")>0;LIGNE('Reference Table PCDO'!$O:$O);"") => SI(TRUE(1)>0;{10, 74, 75, ..., "")*

* 10 = Ligne ou "Arval" est écrit et a partir de 74 c'est toutes les lignes nulles/vides de la colonne G.

MIN=SI(TRUE(1)>0;{10, 74, 75, ..., "")=10 (Donc arval)

INDEX('Reference Table PCDO'!$O:$O;MIN(SI(NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*")>0;LIGNE('Reference Table PCDO'!$O:$O);""))) => INDEX('Reference Table PCDO'!$O:$O;10; "") = Arval

et enfin RECHERCHEV(INDEX('Reference Table PCDO'!$O:$O;MIN(SI(NB.SI(Z2;"*" &'Reference Table PCDO'!$O:$O& "*")>0;LIGNE('Reference Table PCDO'!$O:$O);"")));'Reference Table PCDO'!$O:$P;2;0) => RECHERCHEV("Arval"; 'Reference Table PCDO'!$O:$P;2; 0)

J'ai testé un autre truc mais je suis un peu perdu dans les références des colonnes entre les messages et le fichier transmis. En gros c'est d'utiliser la formule matricielle uniquement pour le cas où "L00", "L99" ou vide puis d'écraser le résultat par sa valeur. Cela fonctionne mais en terme de temps de traitement, je ne sais pas, à voir ...

Donc remplacer :

ElseIf Lease_Code = "L00" Or Lease_Code = "L99" Or Lease_Code = "" Then
Cells(ligne, 6) = Application.VLookup("*" & Klaant_Name_1 & "*", Listing_2, 2, If_Close)

par (il faut actualiser Z80 et $G:$G par les bonnes références) :

ElseIf Lease_Code = "L00" Or Lease_Code = "L99" Or Lease_Code = "" Then
      Cells(ligne, 6).FormulaArray = "=IFERROR(INDEX('Reference Table PCDO'!$G:$G,MIN(IF(COUNTIF(Z80,""*"" &'Reference Table PCDO'!$G:$G& ""*"")>0,ROW('Reference Table PCDO'!$G:$G)))),""OTHERS"")"
      Cells(ligne, 6).value = Cells(ligne, 6).value

Malheureusement, j'ai l'impression que ca ne marche pas non plus .. j'avoue ne pas comprendre pourquoi .. Le résultat est toujours different de la formule Excel.

J'ai aussi fait un test en lancant (meme si le resultat n'est pas bon) la macro sur mon jeu complet de données (les 100 000 donc). J'ai laissé tourné 30 mins et seulement 10 000 lignes avaient été calculées. Le FormulaArray parait donc trop gourmand niveau temps aussi... Ca ne vaut pas le coup de continuer sur cette logique. Je dois absolument trouvé une fonction VBA directement ... Et pas une fonction Excel transposé en VBA visiblement. Mon materiel n'a pas les capacités de calculer ca.

J'avoue arriver à court d'idée de mon coté. Je parcours aussi d'autres forum pour voir si quelqu'un a deja proposé un cas similaire mais je ne trouve rien de pertinent ..