Microsoft 365 VBA rechercher des mots en communs dans deux chaines

[maguie]

Bonjour
Il y a quatre colonnes A ,B,C et D.
Dans la colonne A il y a des libellés banques : exemple (du 25012018 cb Intermarché) variables
Dans la colonne B est vide car attente d'affectation
Dans la colonne C et D ce sont des constantes qui correspondent entre elles par des numéros : exemples pour la colonne c on peut trouver la constante Intermarché a laquelle correspond le numéro 472

La problématique : Mettre au point une macro VBA qui a chaque fois qu'elle détecte un mot en commun entre la colonne A et la colonne C affecte le numéro correspondant dans la colonne B
Exemple : A et B ont en commun le mot Intermarché alors affecté dans la colonne D le numéro correspondant
Dans mon exemple A 1= du 25012019 Intermarché
C1 = Intermarché et D1 = 472 donc B1 = 472
Je souhaiterais préciser que les lignes ne sont pas forcément sur la même ligne comme l'exemple proposé
En fait la macro doit parcourir la colonne constante A et la comparer à la colonne C afin de déterminer le numéro qui sera affecté dans la colonne B mais sur la même ligne que la valeur comparée de la colonne A
Merci pour votre aide

 

[Ethiryn - Glarilak]

Bonjour maguie,

Je peut te proposer deux solutions :
- La première avec les fonctions Excel, INDEX et EQUIV.
- La seconde avec les fonctions VBA.

Première solution :

=INDEX($C:$D;EQUIV($A1;$C:$C;0);2)

La formule cherche la valeur A1 dans la colonne C est renvoie la valeur correspondant à la même ligne en B1.
Il faut entrer cette formule dans chacune des cellules où tu veux que la valeur s'affiche.

Je te mes de post que j'ai rédiger, dans lequel j'expliquer, pour le premier les formules INDEX et EQUIV en détaille. Le second explique l'utilisation des listes déroulantes qui peuvent être utile. Ensuite un lien vers la discussion entière.

Le premier post :

Oui cela est tout à fait possible, avec la formule suivante =INDEX($I$2:$L$23;EQUIV($A2;$I$2:$I$23;0);2)
Il faut changer le "2" en fin de formule selon la colonne qui doit renvoyer la réponse, je m'explique...
- La fonction INDEX renvoie la valeur d'une cellule dans une plage selon la ligne de la cellule dans la plage, et la colonne de la cellule dans la plage, exemple =INDEX($I$2:$L$23;2;2) renvoie la valeur de la cellule J2.
- La fonction EQUIV renvoie la ligne ou la colonne (selon la plage sélection), ou la première occurrence du texte cherche est trouvé, exemple =EQUIV($A2;$I$2:$I$23;0) revoie 1 si ta cellule A2 = AHDM715NSG.

Associé ensemble les deux formules font une recherche dans la plage de donner, la seul modifications à faire dans la formule pour chaque colonne est le numéro de la colonne renvoyé. Dans ta colonne E c'est la colonne 2 de la plage que tu souhaite renvoyer, la 4 pour la colonne F, ...

Une chose auquel il faut faire attention c'est s'y il ne trouve pas du tout d’occurrence (Le code article n'est pas présent dans ta liste), alors il affichera une erreur. Pour se prémunir de cette erreur, il suffi de mettre ta formule dans une autre formule SIERREUR
ce qui donne =SIERREUR(INDEX($I$2:$L$23;EQUIV($A2;$I$2:$I$23;0);2);"Le code article n'existe pas"). Evidemment le texte est personalisable.

Bonne suite de journée Morpheus95

Ethiryn - Glarilak

Le second post :

De rien, si sa t'intéresse tu peux même ajouter une liste déroulante avec tout les choix de code article dans les cellules de la colonne 1.

Voici comment faire :
- Tu sélectionne la plage sur laquelle tu veux mettre la liste déroulante ($A$2:$A$23 dans ton fichier)
- Tu vas dans l'onglet Données
- Dans le cadre Outils de Données tu clique sur Validation des données.
- Dans la fenêtre qui s'ouvre tu choisis dans Options, Autoriser, Liste
- Dans source tu rentre la colonne avec les références ($I$2:$I$23 dans ton fichier)
- Tu clique sur Ok
- Maintenant quant tu clique sur une cellule de la colonne A tu as une petite flèche à droite qui ouvre une liste avec toutes les références possible.
- Le plus de cette méthode, tu ne peut plus entrer une valeur non référencé, ce qui supprime l'erreur avec la formule INDEX et EQUIV

Voilà j'espère que sa te sera utile .

Ethiryn - Glarilak

Lien : Formules fonction SI ET imbriqué


Seconde solution :

La seconde solution utilise les formules VBA suivante :

Application.Index() (Équivalent de la fonction INDEX Excel)
Application.Match() (Équivalent de la fonction EQUIV Excel)

Leurs construction est la même que la construction Excel.

Je peux te fournir la formule toutes faites, mais pour cela il me faudrait un fichier qui me servent d'outil de construction. (Il n'est pas nécessaire que sa soit le fichier que tu édite, un fichier exemple suffit)
Sinon tu dois pouvoir te débrouille avec la formule Excel, soit en l'adaptant en VBA soit en l'utilisant en Excel.

Si tu as besoin d'une réponse plus précise tiens m'en informé. (Un peu flémard ce soir ).

Ethiryn - Glarilak

 

[maguie]

Merci
Je te joins un fichier

 

[Ethiryn - Glarilak]

Bonjour maguie,

Un peu moins flemmard ce soir , j'ai donc concoctée deux petites formules, une VBA et une EXCEL, à prendre aux choix.

VBA :

Public Sub afficher_plan_comptable()
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 2) = Application.Index(Range("C2", Cells(Cells(Rows.Count, 3).End(xlUp).Row, 4)), Application.Match(Right(Cells(i, 1), Len(Cells(i, 1)) - 15), Range("C2", Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3)), 0), 2)
    Next i
End Sub

Il te suffit de coller le code si-dessus dans un module et d'assigner à un bouton sur ta feuille excel pour qu'il fasse le travail tout seul. (La macro s'adapte au nombre de ligne présente en colonne A mais aussi aux tableau de données.
Attention, il y a plusieurs paramètres auquel il faut faire attention, surtout si les plages de tableaux ne sont pas organiser exactement comme dans le document exemple. Dans ce cas, je peut te modifier la formule si tu m'indiquer l'emplacement du tableau relevé banque (Emplacement de la première cellule du tableau) et l'emplacement du tableau de données (Emplacement de la première cellule du tableau).

J'ai moi aussi une question sur le sujet pour les plus expérimentés, peut-on éviter une boucle for dans cette macro ?


Excel :

Il faut préparer plusieurs chose :

- Dans l'onglet Formules Cliquer sur Gestionnaire des Noms, Nouveau entrer un le titre suivant "Libelle", puis dans la case Fait référence à entrer =DECALER(Feuil1!$C$2;;;NBVAL(Feuil1!$C:$C)-1;1)
- Puis refaire pareille en entrant le titre "Tableau_données" et la formule =DECALER(Feuil1!$C$2;;;NBVAL(Feuil1!$C:$C)-1;2).
C'est deux formules permette de définir le tableau de donnés pour la formules en dessous.

Attention si le tableau des données ne se situe pas dans la feuil1 et ou dans la colonne C à D.
Il faut remplacer deux élément :
Feuil1$C$2 par la première cellule de données du tableau (dans le document exemple c'est la cellule C2 = CASINO)
Feuil1$C:$C par la colonne entière de la première cellule du tableau (dans le document exemple c'est la colonne C)

Une fois les deux 'Noms' entrés. Dans les cellules où tu veux que s'affiche le numéro de plan comptable correspondant à la cellule voisine, il faut écrire la formule suivante =INDEX(Tableau_données;EQUIV(DROITE(A2;NBCAR(A2)-15);Libelle;0);2)

Pareille, comme juste avant, il faut remplacer le A2 par la première cellule de la plage de cellule voisine.

Quant le changement est effectuer il ne reste plus qu'a étendre sur toutes les cellules voisines.

La dernière chose, dès que l'on ajoute une ligne dans la liste des relevé, il faut ajouter de nouveau la formule (la copier suffit).

J'espère être clair est précis, si jamais quelque chose est mal compris alors tu peux redemander des précisions si nécessaire.

Ethiryn - Glarilak

 

[maguie]

Bonjour
Super!!!
Un grand merci pour ton aide
Très bonne journée

 

[maguie]

Le seul souci c'est que le programme plante lorsque j'ajoute des données sur la colonne C ou D ? Erreur d'exécution 5

 

[maguie]

Je faisais référence au code vba pour le message d'erreur.

 

[Ethiryn - Glarilak]

Je ne vois pas où est le problème, quant je l’exécute tout fonctionne bien.

Après avoir chercher à crée cette erreur, elle est apparut quant j'ai modifier la colonne Relevé Banque.
La syntaxe de cette colonne est-elle exactement la même que celle du document d'exemple ?

Si ce n'est pas là même, le problème vient peux-être de là.

Ethiryn - Glarilak

 

[maguie]

Tu as raison
Mais serait-il possible d'envisager plusieurs syntaxe sur le relevé de banque
Car sur les exemples c'est un relevé crédit mutuel dont la rubrique cb a cette forme de syntaxe, mais pour les prélèvements cela peut être différents.
Exemple prélèvement URSSAF

 

[maguie]

En fait l'erreur provient du changement de syntaxe sur la colonne A.
Merci de m'éclairer
J'ai tenté une macro FOR EACH …

 

[job75]

Bonjour maguie, Ethiryn - Glarilak,

Voyez le fichier joint avec cette formule matricielle en B2 :

=SIERREUR(PETITE.VALEUR(SI(ESTNUM(CHERCHE([LIBELLE PLAN COMPTABLE];[@[RELEVE BANQUE]]));[NUMERO PLAN COMPTABLE]);1);"")

Le tableau est un tableau Excel structuré (menu INSERTION => Tableau), de cette manière la formule se recopie automatiquement.

Edit : en cas de cellules vides en colonne C il faut utiliser en B2 du fichier (2) :

=SIERREUR(PETITE.VALEUR(SI(([LIBELLE PLAN COMPTABLE]<>"")*ESTNUM(CHERCHE([LIBELLE PLAN COMPTABLE];[@[RELEVE BANQUE]]));[NUMERO PLAN COMPTABLE]);1);"")

A+

 

[patricktoulon]

bonjour sinon en vba tu peux te faire ta propre fonction

la fonction dans un module standard

Public Function codelibélé(cell, rng As Range)
   Dim Cel As Range
   codelibélé = ""
    For Each Cel In rng
        If InStr(cell.Text, Cel.Text) > 0 Then codelibélé = Cel.Offset(, 1).Value: Exit For
    Next
End Function

et la formule dans les cellule de la colonne "B"
exemple en B2
=codelibélé(A2;C:C)

 

[maguie]

Bonjour maguie, Ethiryn - Glarilak,

Voyez le fichier joint avec cette formule matricielle en B2 :

=SIERREUR(PETITE.VALEUR(SI(ESTNUM(CHERCHE([LIBELLE PLAN COMPTABLE];[@[RELEVE BANQUE]]));[NUMERO PLAN COMPTABLE]);1);"")

Le tableau est un tableau Excel structuré (menu INSERTION => Tableau), de cette manière la formule se recopie automatiquement.

A+

Cela fonctionne à merveille. Mais il faudrait une version sous VBA. Car les personnes qui vont utiliser le fichier ne sont pas vraiment branchées excel.
Donc un petit clic. Import du fichier banque, fichier plan comptable cela je sais faire. Mais je bloque sur la mise en commun de caractères. A savoir pourquoi?

 

[Ethiryn - Glarilak]

Bonjour maguie, patricktoulon et job75

Pour résoudre le problème qui est issue de ma proposition, il faudrait mettre un élément unique dans chaque cellule du tableau des relevés.
Exemple : CB DU 25012019 : AMAZON et URSSAF DU 25012019 : INTERMARCHE, le caractère ":" permettrais de repère pour savoir où se trouve le libelle.
La formule donnerais ceci :

Public Sub afficher_plan_comptable()
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 2) = Application.Index(Range("C2", Cells(Cells(Rows.Count, 3).End(xlUp).Row, 4)), Application.Match(Right(Cells(i, 1), Len(Cells(i, 1)) - InStr(1, Cells(i, 1), ":", vbBinaryCompare) - 1), Range("C2", Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3)), 0), 2)
    Next i
End Sub

Cela fonctionne à merveille. Mais il faudrait une version sous VBA. Car les personnes qui vont utiliser le fichier ne sont pas vraiment branchées excel.
Donc un petit clic. Import du fichier banque, fichier plan comptable cela je sais faire. Mais je bloque sur la mise en commun de caractères. A savoir pourquoi?

Sait-tu que tu peux bloquer la modification de cellule par un mot de passe ?

Ethiryn - Glarilak

 

[job75]

Re, salut patricktoulon,

En VBA, si l'on veut aller vite sur de grands tableaux, il faut utiliser des tableaux VBA et le Dictionary :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tablo, d As Object, i&, s, j%
With [A1].CurrentRegion
    tablo = .Resize(, 4) 'matrice, plus rapide
    '---liste des libellés---:end
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbTextCompare 'la casse est ignorée
    For i = 2 To UBound(tablo)
        If tablo(i, 3) <> "" Then d(tablo(i, 3)) = tablo(i, 4) 'mémorise le numéro
    Next
    '---analyse des mots des relevés---
    For i = 2 To UBound(tablo)
        s = Split(tablo(i, 1))
        tablo(i, 2) = "" 'RAZ
        For j = 0 To UBound(s)
            If d.exists(s(j)) Then tablo(i, 2) = d(s(j)): Exit For
    Next j, i
    '---restitution---
    If FilterMode Then ShowAllData 'si la feuille est filtrée
    Application.EnableEvents = False 'désactive les évènements
    .Columns(2) = Application.Index(tablo, , 2)
    Application.EnableEvents = True 'réactive les évènements
End With
End Sub

Fichier .xlsm joint.

A+