Excel – VBA: traite des éléments d'un tableau lors de la search de matchs

Je travaille sur un programme dont l'objective est d'identifier les parties d'une adresse dans une database. L'input est une colonne, chaque cellule contenant un mot / numéro provenant de l'adresse. D'autre part, dans la database, chaque cellule contient une information complète de quelques mots.

Voici un exemple : entrez la description de l'image ici

Voici ce que je fais déjà:
1 / Looping dans la colonne de la database (ici de G3 à G7) et activez la cellule actuelle.
2 / Pour chaque cellule de (B2: B9), searchz une correspondance avec ActiveCell
3 / Si une correspondance est trouvée, ajoutez 10 points à une cellule, lorsque la boucle de la colonne B est terminée, passez à une autre cellule de la database. Donc dans cet exemple, il y aurait 3 matches dans G3, donc 30 points.

C'est bon, mais je veux le rendre plus précis en tenant count de la position des mots "Tour des finances générales" serait repéré comme correspondant à la database.

Pour ce faire, j'ai l'intention de split le contenu des cellules dans G dans un tableau.

Voici la façon dont il serait amélioré:
1 / Looping dans la colonne de database de G2: G7. Divisez la première cellule dans un tableau de n mots (3 dans ce cas): "Général / Finances / Tour"
2 / Rechercher une correspondance entre le premier mot du tableau et les éléments de la colonne B. Si non, comparez, passez à l'élément suivant (B2, B3, … B9). Si vous ne parvenez toujours pas à B9, passez au deuxième élément du tableau (Finance) et continuez.
Si correspondance (ici entre "Général" (premier élément du tableau) et B2), regardez s'il y a correspondance entre l'élément suivant du tableau et l'élément suivant de la colonne B ("Finance" et "Finance"). Si oui, faites-le à nouveau ("Tower" et "Tower") et ainsi de suite.

De cette façon, "General Finance" serait repéré, puis "General Finance Tower", donnant plus de précision à mon programme.

Voici ma question, plus liée à la programmation:

Je sais comment split les colonnes G en arrays, mais je ne sais pas comment naviguer. Si, au lieu d'un tableau, il s'agissait de N cellules différentes, je commencerais par la cellule 1, l'activer, puis j'utilisais un décalage (1,0) pour aller à la cellule suivante, décalé (2,0) pour aller deux cellules plus loin, et ainsi de suite, à la search de matches dans tous les cas. Comment faire avec l'utilisation d'un tableau? Comment passer à l'élément suivant?

ssortingngData = Split(ActiveCell.Value, " ") For i = LBound(ssortingngData) To UBound(ssortingngData) If Match(ActiveCell, ssortingngData(i)) Then ... else End If Next i

Cela me permettrait du premier élément à la dernière mais ne me proposerait pas vraiment d'options pour naviguer (par exemple, en recherchant une correspondance directement avec le second élément si l'élément actuel est en correspondance).

Merci d'avance pour votre suggestion, cela aiderait vraiment!

eh oui, j'ai écrit le code pour vous qui marquera en fonction de ma compréhension de la complexité de votre problème. L'input et la sortie ressemblent à ceci: entrée et sortie

et le code …
x) a tant de commentaires que vous devriez pouvoir le modifier facilement au cas où quelque chose ne serait pas correct.

 Option Explicit Sub DatabaseVsInputComparison_Scoring() Dim ws As Worksheet ' worksheet instance Dim i&, j&, k&, x As Long ' iterators Dim db_startRow As Long ' --> Dim db_startColumn As Long ' --> These variables will Dim db_lastRow As Long ' --> store the database table Dim db_lastColumn As Long ' --> boundries Dim inp_startRow As Long ' starting row of the data in INPUT column Dim inp_lastRow As Long ' last row in the INPUT column Dim inp_column As Long ' the column number of the INPUT column Dim rng As Range ' active db range reference Dim inp_rng As Range ' active input ref Dim score As Long ' store temporary score ' // setters Set ws = Sheets("Sheet1") ' set reference db_startRow = 3 ' set starting row for the database data db_startColumn = 7 ' set starting column for the database data inp_startRow = 2 ' set starting row of the data in input column inp_column = 2 ' set starting row for the input column ' // getters ' get the boundries of the database table db_lastRow = ws.Cells(Rows.Count, db_startColumn).End(xlUp).Row db_lastColumn = ws.Cells(db_startRow, Columns.Count).End(xlToLeft).Column inp_lastRow = ws.Cells(Rows.Count, inp_column).End(xlUp).Row ' iterate through the database table For i = db_startRow To db_lastRow ' each ROW For j = db_startColumn To db_lastColumn ' each COLUMN score = 0 ' reset the score for each cell in the database set Set rng = ws.Cells(i, j) Dim splitted ' array storing each word of the "active" cell splitted = Split(rng.Value, " ") If UBound(splitted) > -1 Then For k = inp_startRow To inp_lastRow ' each input column data cell Set inp_rng = ws.Cells(k, inp_column) ' check if the first word has got a match in the input column If StrComp(CStr(splitted(0)), inp_rng.Value, 1) = 0 Then score = 12 ' set initial score ' this is where you want to iterate through the rest of the active database cell ' and check if the next words match, right? For x = 1 To UBound(splitted) ' now youre checking the next word in the splitted array ' against the next word in the input column If StrComp(CStr(splitted(x)), inp_rng.Offset(x, 0).Value, 1) = 0 Then ' if the match is found you want to keep on checking ' and incrementing the score score = score + 12 ' if no match you want to exit the loop ' > no extra score Else Exit For End If Next x End If Set inp_rng = Nothing Next k ' score calculation ' if max score reached then add extra 3 to the score If score = ((UBound(splitted) + 1) * 12) Then score = score + 3 rng.Offset(0, 5).Value = score Set rng = Nothing End If Next j Next i End Sub que Option Explicit Sub DatabaseVsInputComparison_Scoring() Dim ws As Worksheet ' worksheet instance Dim i&, j&, k&, x As Long ' iterators Dim db_startRow As Long ' --> Dim db_startColumn As Long ' --> These variables will Dim db_lastRow As Long ' --> store the database table Dim db_lastColumn As Long ' --> boundries Dim inp_startRow As Long ' starting row of the data in INPUT column Dim inp_lastRow As Long ' last row in the INPUT column Dim inp_column As Long ' the column number of the INPUT column Dim rng As Range ' active db range reference Dim inp_rng As Range ' active input ref Dim score As Long ' store temporary score ' // setters Set ws = Sheets("Sheet1") ' set reference db_startRow = 3 ' set starting row for the database data db_startColumn = 7 ' set starting column for the database data inp_startRow = 2 ' set starting row of the data in input column inp_column = 2 ' set starting row for the input column ' // getters ' get the boundries of the database table db_lastRow = ws.Cells(Rows.Count, db_startColumn).End(xlUp).Row db_lastColumn = ws.Cells(db_startRow, Columns.Count).End(xlToLeft).Column inp_lastRow = ws.Cells(Rows.Count, inp_column).End(xlUp).Row ' iterate through the database table For i = db_startRow To db_lastRow ' each ROW For j = db_startColumn To db_lastColumn ' each COLUMN score = 0 ' reset the score for each cell in the database set Set rng = ws.Cells(i, j) Dim splitted ' array storing each word of the "active" cell splitted = Split(rng.Value, " ") If UBound(splitted) > -1 Then For k = inp_startRow To inp_lastRow ' each input column data cell Set inp_rng = ws.Cells(k, inp_column) ' check if the first word has got a match in the input column If StrComp(CStr(splitted(0)), inp_rng.Value, 1) = 0 Then score = 12 ' set initial score ' this is where you want to iterate through the rest of the active database cell ' and check if the next words match, right? For x = 1 To UBound(splitted) ' now youre checking the next word in the splitted array ' against the next word in the input column If StrComp(CStr(splitted(x)), inp_rng.Offset(x, 0).Value, 1) = 0 Then ' if the match is found you want to keep on checking ' and incrementing the score score = score + 12 ' if no match you want to exit the loop ' > no extra score Else Exit For End If Next x End If Set inp_rng = Nothing Next k ' score calculation ' if max score reached then add extra 3 to the score If score = ((UBound(splitted) + 1) * 12) Then score = score + 3 rng.Offset(0, 5).Value = score Set rng = Nothing End If Next j Next i End Sub de Option Explicit Sub DatabaseVsInputComparison_Scoring() Dim ws As Worksheet ' worksheet instance Dim i&, j&, k&, x As Long ' iterators Dim db_startRow As Long ' --> Dim db_startColumn As Long ' --> These variables will Dim db_lastRow As Long ' --> store the database table Dim db_lastColumn As Long ' --> boundries Dim inp_startRow As Long ' starting row of the data in INPUT column Dim inp_lastRow As Long ' last row in the INPUT column Dim inp_column As Long ' the column number of the INPUT column Dim rng As Range ' active db range reference Dim inp_rng As Range ' active input ref Dim score As Long ' store temporary score ' // setters Set ws = Sheets("Sheet1") ' set reference db_startRow = 3 ' set starting row for the database data db_startColumn = 7 ' set starting column for the database data inp_startRow = 2 ' set starting row of the data in input column inp_column = 2 ' set starting row for the input column ' // getters ' get the boundries of the database table db_lastRow = ws.Cells(Rows.Count, db_startColumn).End(xlUp).Row db_lastColumn = ws.Cells(db_startRow, Columns.Count).End(xlToLeft).Column inp_lastRow = ws.Cells(Rows.Count, inp_column).End(xlUp).Row ' iterate through the database table For i = db_startRow To db_lastRow ' each ROW For j = db_startColumn To db_lastColumn ' each COLUMN score = 0 ' reset the score for each cell in the database set Set rng = ws.Cells(i, j) Dim splitted ' array storing each word of the "active" cell splitted = Split(rng.Value, " ") If UBound(splitted) > -1 Then For k = inp_startRow To inp_lastRow ' each input column data cell Set inp_rng = ws.Cells(k, inp_column) ' check if the first word has got a match in the input column If StrComp(CStr(splitted(0)), inp_rng.Value, 1) = 0 Then score = 12 ' set initial score ' this is where you want to iterate through the rest of the active database cell ' and check if the next words match, right? For x = 1 To UBound(splitted) ' now youre checking the next word in the splitted array ' against the next word in the input column If StrComp(CStr(splitted(x)), inp_rng.Offset(x, 0).Value, 1) = 0 Then ' if the match is found you want to keep on checking ' and incrementing the score score = score + 12 ' if no match you want to exit the loop ' > no extra score Else Exit For End If Next x End If Set inp_rng = Nothing Next k ' score calculation ' if max score reached then add extra 3 to the score If score = ((UBound(splitted) + 1) * 12) Then score = score + 3 rng.Offset(0, 5).Value = score Set rng = Nothing End If Next j Next i End Sub