IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Vlookup qui cherche des strings partiels [XL-2010]


Sujet :

Macros et VBA Excel

  1. #1
    Membre à l'essai
    Homme Profil pro
    Consultant fonctionnel
    Inscrit en
    Mars 2013
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Consultant fonctionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2013
    Messages : 4
    Par défaut Vlookup qui cherche des strings partiels
    Bonjour,

    Je bloque sur le problème suivant:

    Colonne A: données texte (nb de lignes pouvant varier!)
    Colonne B: vlookup qui cherche des extaits de texte dans la colonne A et renvoie un numéro correspondant à un tableau sur les colonnes C et D
    Colonne C: extaits de texte à rechercher
    Colonne D: numéro correspondant à chaque extrait de texte

    Exemple:
    Colonne A: "Prélèvement SFR"
    Colonne C: "SFR"
    Colonne D: 1

    Je voudrais que la colonne B renvoie donc 1 (par vlookup je pense). Attention, le nombre de lignes de la colonne A et du tableau en C et D varient à chaque utilisation. Je ne sais pas si il faut nécessairement coder en VBA ou s'il y a une fonction excel directement utilisable (genre un if avec un vlookup dedans).

    Je bute la dessus depuis deux jours et ça me rends très triste

    Merci pour votre aide!

    Kilogram76 (enfin j'essaie...)

  2. #2
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 173
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Une petite remarque sur
    Colonne B: vlookup qui cherche des extaits de texte dans la colonne A et renvoie un numéro correspondant à un tableau sur les colonnes C et D
    Une formule dans Excel ne peut envoyer un résultat dans une autre cellule que celle où elle se trouve seul le VBA le permet.
    Toutefois, rien ne t'interdit d'utiliser RECHERCHEV dans une cellule en colonne C et une en colonne D pour obtenir la valeur recherchée.
    C'est ce que l'on fait dans une facturation pour avoir le nom, l'adresse, et localité d'une personne.

    Bonjour,
    Pourquoi n'utilises-tu pas les filtres élaborés ou avancés d'excel. Je pense d'après ta question que cela devrait répondre à ta demande
    Dans ta zone des critères tu mets l'étiquette de la colonne A et comme critère (cellule en dessous de l'étiquette) *SFR*
    Si tu veux le n° de ligne de la ligne trouvée, tu ajoutes une colonne avec la formule LIGNE() et tu donnes comme titre à la colonne par exemple Ligne
    et si tu veux exporter les données dans la zone d'exportation Le nom de l'étiquette de la colonne A et Ligne.
    Voir tutoriel sur Les filtres avancés ou élaborés dans Excel
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  3. #3
    Membre à l'essai
    Homme Profil pro
    Consultant fonctionnel
    Inscrit en
    Mars 2013
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Consultant fonctionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2013
    Messages : 4
    Par défaut Recherche de strings partiels et recherchev
    Re-bonjour,

    Merci pour tes réponses et le tuto sur les filtres avancés. Je suis en train de le lire et je devine le potentiel de l'outil! Mais je ne pense pas qu'il soit nécessaire de les utiliser pour mon problème.

    Quand tu dis:

    Citation Envoyé par Philippe Tulliez Voir le message
    Une formule dans Excel ne peut envoyer un résultat dans une autre cellule que celle où elle se trouve seul le VBA le permet.
    en fait, je ne souhaite pas envoyer le résultat dans C ou D. C et D contiennent juste le tableau de correspondance (SFR -> 1). Je voudrais donc que la formule if/recherchev (?) dans la colonne B renvoie le 1 (toujours dans la colonne B) parce que excel a trouvé SFR dans le texte de la colonne A.

    Je me permets de joindre un tout petit fichier qui rendra tout clair.

    Merci!
    JC
    Fichiers attachés Fichiers attachés

  4. #4
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 173
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Le problème c'est que malgré ton classeur, l'explication que tu donnes ne m'éclaire pas plus parce-que tu ne dis pas quelle est la liste de données de référence des données.
    Si c'est que tu as en rouge dans la plage A3:A6 qui doit aller voir dans la plage C1115 et renvoyer la valeur qui est en colonne 2. Difficile de le faire sans VBA.
    Si c'est partir des chaînes de caractères se trouvant en C11:C15 et qui sont des mots se trouvant dans la base de données en A3:A6 oui c'est possible.
    Si j'ai compris la fonction doit renvoyer 5 si la chaîne n'est pas trouvée
    Une formule qui renvoie la position dans A3:A6 de la chaîne partielle se trouvant en C11
    Exemple compatible 2003-2010
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI(ESTNA(EQUIV("*" & C11 & "*";$A$3:$A$6;0));5;EQUIV("*" & C11 & "*";$A$3:$A$6;0))
    Exemple plus léger mais utilisable à partir de la version 2007
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SIERREUR(EQUIV("*" & C11 & "*";$A$3:$A$6;0);5)
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  5. #5
    Membre à l'essai
    Homme Profil pro
    Consultant fonctionnel
    Inscrit en
    Mars 2013
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Consultant fonctionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2013
    Messages : 4
    Par défaut
    Bonjour,

    Pardon pour le manque de clarté. Ce que tu dis est exactement l'énoncé du problème et tu me confirmes donc qu'il n'y a pas d'autres solutions que VBA.

    Citation Envoyé par Philippe Tulliez Voir le message
    quelle est la liste de données de référence des données.
    Si c'est que tu as en rouge dans la plage A3:A6 qui doit aller voir dans la plage C1115 et renvoyer la valeur qui est en colonne 2. Difficile de le faire sans VBA
    Mais ta réponse me donne des idées pour le faire en VBA. Un code du genre: tester avec equiv les lignes de la colonne A, une par une, contre les lignes de la colonne C, et renvoyer les nombres de la colonne D quand il y a une équivalence. Je vais le tenter avec mes bases rudimentaires en VBA.

    Mais si toi ou quelqu'un d'autre a le courage d'essayer en VBA, ce serait super. J'attends deux ou trois jours pour voir et si pas de réponse, je marquerai le post comme résolu.

    Merci!
    JC

  6. #6
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 173
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Si j'ai bien compris ton problème voici une solution qui demande encore à être améliorée (test si plusieurs mots sont en rouge, possibilité de placer dans la formule une constante plutôt que faire référence à une cellule, tester qu'une seule cellule est référencée, etc...)
    Nous partons du fait que la fonction fait référence à une cellule contenant une valeur alphanumérique dont un mot est en couleur (par défaut Rouge) et qu'il faut renvoyer la position de ce mot qui est en rouge dans une plage.
    Exemple d'une fonction personnalisée
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Function FindWord(Text As Range, Table As Range, Optional color As Double = vbRed) As Long
     Dim c As Integer, Word As String
     Dim fn As WorksheetFunction: Set fn = Application.WorksheetFunction
     For c = 1 To Len(Text)
      With Text
       If .Characters(c, 1).Font.color = vbRed Then Word = Word & Mid(Text, c, 1)
      End With
     Next
     On Error Resume Next
     FindWord = fn.Match(Trim(Word), Table, 0)
     If Err Then FindWord = 0
    End Function
    On l'appelle depuis excel
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =FindWord(B2;$H$2:$H$6)
    La formule renvoie 0 si aucun mot dans la cellule B2 n'est en rouge et si le mot qui était en rouge n'est pas trouvé dans la plage $H$2:$H$6
    Pour l'instant si plusieurs mots sont en rouge, la variable Word contient la concaténation des mots en couleur.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  7. #7
    Membre Expert
    Homme Profil pro
    Retraité
    Inscrit en
    Avril 2011
    Messages
    1 858
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Retraité

    Informations forums :
    Inscription : Avril 2011
    Messages : 1 858
    Par défaut
    Bonjour Jean-Christophe,
    Bonjour Philippe,

    Si j'ai bien compris, il ne s'agit pas de rechercher des mots avec une police en rouge mais la présence de mots clés dans les textes de la feuille 1 et de noter un numéro associé à ces mots clés.
    Une solution avec la méthode Find
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    Sub Test()
    Dim PlageS As Range, PlageC As Range, Cel As Range, C As Range
    Dim firstAddress As String
        With Worksheets("Feuil2")
            Set PlageS = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
        End With
        With Worksheets("Feuil1")
            Set PlageC = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
        End With
        For Each Cel In PlageS
            Set C = PlageC.Find(Cel, LookIn:=xlValues, lookat:=xlPart)
            If Not C Is Nothing Then
                firstAddress = C.Address
                Do
                    C.Offset(0, 1).Value = Cel.Offset(0, 1).Value
                    Set C = PlageC.FindNext(C)
                Loop While Not C Is Nothing And C.Address <> firstAddress
            End If
        Next Cel
        For Each Cel In PlageC
            If Cel.Offset(0, 1).Value = "" Then Cel.Offset(0, 1).Value = 5
        Next Cel
    End Sub
    Cordialement.
    Fichiers attachés Fichiers attachés

  8. #8
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 173
    Billets dans le blog
    53
    Par défaut
    Bonjour gFZT82,
    C'est ce que j'avais cru comprendre au départ d'où ma première proposition mais la réponse donnée ensuite m'a laissé supposer le contraire.
    Juste pour le fun, une solution sans VBA qui suppose qu'un mot de la liste ne peut pas être repris deux fois dans la phrase donc ne s'applique pas dans ce cas puisque la dernière phrase contient deux mots de cette liste.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMMEPROD( (NON(ESTERR(CHERCHE(ListMots;A3)>1))) * (LIGNE(ListMots)))-LIGNE(ListMots)+1
    ListMots est la plage contenant les mots cherchés et fait référence à C11:C15, la phrase est dans la cellule A3.
    Si pas trouvé renvoie un chiffre négatif qui est égal à la première ligne de la plage ListMots
    [EDIT]
    Petite variante avec les mêmes contraintes mais qui renvoie le nombre de lignes de la plage ListMots si un des mots de cette plage n'est pas trouvé (donc 5 dans le cas présent).
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI(SOMMEPROD( (NON(ESTERR(CHERCHE(ListMots;A3)>1))) * (LIGNE(ListMots)));SOMMEPROD( (NON(ESTERR(CHERCHE(ListMots;A3)>1))) * (LIGNE(ListMots)))-LIGNE(ListMots)+1;LIGNES(ListMots))
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  9. #9
    Membre à l'essai
    Homme Profil pro
    Consultant fonctionnel
    Inscrit en
    Mars 2013
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Consultant fonctionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2013
    Messages : 4
    Par défaut


    Merci beaucoup, Philippe etgFZT82!!!

    A vous deux, vous avez plus que résolu mon problème en me donnant des alternatives qui marchent!

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Additionner deux String qui sont des nombres
    Par JDev_ dans le forum Requêtes et SQL.
    Réponses: 1
    Dernier message: 24/05/2012, 13h00
  2. Des string qui se calculent tout seuls ?
    Par xabidof dans le forum SQLite
    Réponses: 2
    Dernier message: 19/04/2010, 14h31
  3. Addition de deux String qui sont des nombres
    Par ramon.dekker dans le forum Débuter avec Java
    Réponses: 5
    Dernier message: 06/10/2008, 17h03
  4. Réponses: 1
    Dernier message: 21/09/2006, 12h28
  5. [LG]un programme pascal qui traduit des chiffes en lettres
    Par hartmann n'goran dans le forum Langage
    Réponses: 6
    Dernier message: 26/04/2004, 18h23

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo