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

 

[patricktoulon]

bonjour job75
c'est pas les variables tableaux ou dico qui accélère dans ce cas présent mais le fait que c'est déclenché par l’événement change donc 1 cellule
tandis qu'une formule c'est la colonne entière qui est réévaluée avec le sheet calculate

et la lecture en boucle d'une colonne comme dans le cas présent est aussi rapide que la lecture en boucle d'une variable tableau (je dis bien en LECTURE!!!)

 

[job75]

c'est pas les variables tableaux ou dico qui accélère dans ce cas présent mais le fait que c'est déclenché par l’événement change donc 1 cellule

Vous n'y êtes pas du tout, qu'il y ait une cellule de modifiée ou un milliard la durée d'exécution est la même puisque c'est toute la colonne B du tableau qui est recalculée par la macro.

 

[job75]

Deux choses :
1) j'ai édité mon post #11 pour le cas où il y a des cellules vides en colonne C

2) j'ai exécuté cette macro sur le fichier (2) post #11 et sur le fichier post #15 :

Sub test()
Dim t
t = Timer
[A2:A5].Copy [A2:A10001] 'copier-coller
MsgBox Timer - t
End Sub

- post #11 (formules) => 23 secondes

- post #15 => 0,3 seconde, y a pas photo.

 

[maguie]

Bonjour à vous tous et un grand merci pour votre aide.
Toutes vos propositions fonctionnent à merveille bien qu'elles soient différentes les unes des autres.
Encore un grand merci pour m'avoir consacré de votre temps .
Très bon Week end à vous tous
Mais méthode dictionary super!!!!

 

[maguie]

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+

Bonjour,
La méthode fonctionne très bien
Par contre serait-il possible de modifier le code afin d'accepter des mots composés dans le plan comptable
Par exemple, serait il possible de modifier le code vba pour que dans le libellé plan comptable on puisse écrire " fournisseur Amazon" pour que ce dernier puisse être mis en commun avec le libellé "cb du ... ... .. Amazon " dans la rubrique relevé banque.
Ensuite, pourrais-tu m'indiquer des ouvrages qui traitent du thème dictionary et variables tableaux.
Par avance merci

 

[job75]

Bonjour maguie,

Le code VBA que j'ai donné ne permet pas de rechercher des expressions de plusieurs mots.

Contentez-vous alors de la solution par formules matricielles de mon post #11 qui elles les acceptent.

Pour les ouvrages sur les tableaux VBA et le Dictionary possible qu'il en existe, faites une recherche sur le web.

A+

 

[maguie]

Bonsoir
OK
Merci beaucoup

 

[patricktoulon]

ben en fait tout dépend si on peu retrouver le mot "fournisseur" dans "cb du ... ... .. Amazon " ou pas
si c'est le cas tu risque la méprise
exemple
"cb du ... ... .. fournisseur Amazon "
"cb du ... ... .. fournisseur ebay"
"cb du ... ... .. Amazon "
si je cherche avec le mot fournisseur je vais chopper les deux premiers dans le même item du dico et c'est pas bon
si tu cherche amazon tu aura les deux bons

donc non ça ne serait pas pérenne
ou alors monter un regex