Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Microsoft 365 VBA rechercher des mots en communs dans deux chaines

M

maguie

XLDnaute Junior
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
 
Trier par date Trier par votes

Ethiryn - Glarilak

XLDnaute Nouveau
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 :

Le second post :


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
 
Dernière édition:
Vote positif 0 Vote négatif

Ethiryn - Glarilak

XLDnaute Nouveau
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 :
VB: 
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
 
Vote positif 0 Vote négatif

Ethiryn - Glarilak

XLDnaute Nouveau
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
 
Vote positif 0 Vote négatif
M

maguie

XLDnaute Junior
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
 
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Bonjour maguie, Ethiryn - Glarilak,

Voyez le fichier joint avec cette formule matricielle en B2 :
Code: 
=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) :
Code: 
=SIERREUR(PETITE.VALEUR(SI(([LIBELLE PLAN COMPTABLE]<>"")*ESTNUM(CHERCHE([LIBELLE PLAN COMPTABLE];[@[RELEVE BANQUE]]));[NUMERO PLAN COMPTABLE]);1);"")

A+
 

Pièces jointes

  • ESSAI RECHERCHE(1).xlsx
    17.4 KB · Affichages: 18
  • ESSAI RECHERCHE(2).xlsx
    17.4 KB · Affichages: 21
Dernière édition:
Vote positif 0 Vote négatif

patricktoulon

XLDnaute Barbatruc
bonjour sinon en vba tu peux te faire ta propre fonction

la fonction dans un module standard
VB: 
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)
 
Vote positif 0 Vote négatif
M

maguie

XLDnaute Junior
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?
 
Vote positif 0 Vote négatif

Ethiryn - Glarilak

XLDnaute Nouveau
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 :
VB: 
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

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

Ethiryn - Glarilak
 
Dernière édition:
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Re, salut patricktoulon,

En VBA, si l'on veut aller vite sur de grands tableaux, il faut utiliser des tableaux VBA et le Dictionary :
VB: 
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+
 

Pièces jointes

  • ESSAI RECHERCHE VBA(1).xlsm
    22.6 KB · Affichages: 37
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

G
  • Question
Microsoft 365 creer un programme d ordonnancement d une chaine de peinture
Réponses
2
Affichages
420
gouedz
G
N
  • Résolu(e)
XL 2021 comment faire recherche dans une colonne et ligne
Réponses
5
Affichages
376
job75
K
  • Question
Microsoft 365 vba :Vlookup + incrémentation d'une plage de donnée
Réponses
1
Affichages
131
vgendron
I
  • Question
Microsoft 365 Filtre dans deux colonnes
Réponses
6
Affichages
436
iliess
I
C
  • Question
XL 2021 Valeurs en fonction de l'âge (jours)
Réponses
4
Affichages
210
Dranreb
M
  • Résolu(e)
XL 2016 formules sous VBA
Réponses
9
Affichages
304
vgendron
G
  • Question
XL 2019 additionner des cellules en fonction
Réponses
7
Affichages
212
JHA
J
  • Résolu(e)
XL 2016 Erreur dans code VBA
4 5 6
Réponses
76
Affichages
2 K
patricktoulon
Y
  • Question
Microsoft 365 VBA - Basculer des lignes d'onglet en onglet selon un critère
2
Réponses
18
Affichages
685
yanus
Y
C
  • Question
Microsoft 365 MFC colonnes + description +date du jour
Réponses
5
Affichages
293
fanfan38
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…