grosyouyou, finalement le résultat est à afficher dans une seconde feuille dénommée "Recherche". Vous allez pouvoir comparer ce que j'ai changé entre ma première version et cette version.
La première chose à faire dans l'énoncé d'un problème est de fournir la déclaration des constantes. Cela permet immédiatement de comprendre qu'il y a deux feuilles :
- "Feuil1" de données de référence en lecture seule : c'est la source.
- "Recherche", la feuille des mots clés à chercher ainsi que la zone des résultats : c'est la cible [Target].
De plus ces constantes permettent tout de suite de savoir où trouver les données et où afficher le résultat. C'est une étape indispensable dans la résolution du problème même si on peut penser que la solution a l'air plus longue. En fait cela documente le programme et permet de mieux le comprendre si on doit le faire évoluer dans quelques mois.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| Option Explicit
Public Const indSheetSource = 1 ' Source : Données de référence
Public Const rowStart = 1
Public Const rowEnd = rowStart + 9
Public Const colValue = 1
Public Const colWord = colValue + 1 ' Colonne X
Public Const indSheetTarget = 2 ' Cible : Mot clé à chercher et résultat
Public Const rowKeywordBegin = 5
Public Const rowKeywordEnd = 7
Public Const colKeyword = 2 ' Colonne des mots clés
Public Const colResult = colKeyword + 1 ' colonne appelée "X X"
Sub FindWords()
Dim indRow As Integer, strWord As String
With Worksheets(indSheetTarget)
For indRow = rowKeywordBegin To rowKeywordEnd
strWord = .Cells(indRow, colKeyword)
.Cells(indRow, colResult) = SearchWord(strWord)
Next
End With
End Sub
Function SearchWord(ByVal strWord As String) As String
Const strSep = ";" ' Pour un retour-chariot remplacer par vbCrLf
Dim indRow As Integer, strResult As String, nbrOccurence As Integer
nbrOccurence = 0
strResult = ""
With Worksheets(indSheetSource)
For indRow = rowStart To rowEnd
If .Cells(indRow, colWord) = strWord Then
If nbrOccurence > 0 Then
strResult = strResult + strSep
End If
strResult = strResult + CStr(.Cells(indRow, colValue)) ' Concatenate
nbrOccurence = nbrOccurence + 1
End If
Next
End With
SearchWord = strResult
End Function |
Usage de SearchWord() et résultat attendu
Dans la fenêtre d'Exécution immédiate (Ctlr+G) du VBE, copier-coller et valider par ENTER :
Notez le pluriel à la fin de Words.
Dans la feuille n° 2 "Recherche", le résultat suivant s'affiche de C5 à C7 soit de R5C3 à R7C3 :
Quand on programme en VBA, on a tout intérêt de basculer en notation R1C1.
C-a-d éviter Range("A1"). Préférez Cells(1,1) plus performant.
Test de la fonction SearchWord()
On a changé Sub SearchWord() en Function retournant le résultat de la recherche.
Cela veut dire que l'on peut directement la tester dans la fenêtre d'Exécution immédiate :
1;3;5;8
Dans la procédure FindWords(), on appelle SearchWord(strWord) dans une boucle For Next pour chaque mot clé de la rangée 5 à la rangée 7 en colonne 2.
On est passé au séparateur ";" dans la liste des résultats concaténés. Voir la constante :
Const strSep = ";" ' Pour un retour-chariot remplacer par vbCrLf
Référence la première solution.
SearchWord() dans une formule d'une cellule
Dans la feuille de calcul, dupliquons, dans la même colonne "B", le dernier mot-clé "route" juste dessous, de B7 à B8 soit de R7C2 à R8C2.
Dans la cellule de résultat C8 soit R8C3, entrons la formule inédite :
Quand on valide la formule, la cellule présente le résultat :
SearchWord() peut donc être utilisée comme nouvelle fonction dans les formules de cellules !
On aurait pu aisément dupliquer cette formule dans toute la colonne C de résultat plutôt que de coder la boucle For Next dans FindWords().
Optimisation
Une fois que tout fonctionne comme souhaité, vous pouvez optionnellement optimiser le cœur de la recherche dans la fonction SearchWord() en intégrant la solution de jfontaine.
Quand la plage de recherche sera beaucoup plus importante (plusieurs centaines de rangées), vous pourrez ainsi mesurer si on gagne du temps avec Rg.Find(StrValeur) et combien.
Dans un premier temps c'est la structuration du programme qui importe. D'abord les constantes puis des procédures Sub ou Function courtes faciles à mettre au point avec des noms de variables significatifs. Ensuite on peut optimiser et mesurer la performance.
___________
Si la discussion est résolue, vous pouvez cliquer sur le bouton 
En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert 
ci-dessous.
rechercher une valeur et concaténer
Répondre avec citation
Envoyé par grosyouyou
Partager