Trouver toutes les cellules du classeur du terme cherché

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Les cellules du terme cherché

Avec ce nouveau développement VBA Excel, nous proposons de monter une solution capable de pointer sur toutes les cellules du classeur abritant le terme ou les mots clés recherchés par l'utilisateur.



Sur l'exemple illustré par la capture, dans une cellule de police à la couleur rouge, l'utilisateur saisit un terme de recherche. Dès lors, il clique sur le bouton (Trouver) placé sur la droite. Une première boîte de dialogue lui demande de cliquer sur la cellule contenant le terme cherché. Après l'avoir validé, une seconde boîte de dialogue (InputBox) lui demande de désigner la première cellule à partir de laquelle il est question de dresser la liste de tous les hyperliens conduisant vers les cellules du classeur embarquant ce terme.

Classeur Excel à télécharger
Pour mener à bien cette étude, nous suggérons d'appuyer les travaux sur un classeur Excel abritant plusieurs feuilles ainsi que cette feuille d'accueil pour la recherche.

• Télécharger le classeur les-dates-et-les-heures.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la commande Propriétés,
• En bas de la boîte de dialogue qui suit, cocher la case Débloquer et valider par le bouton Ok,
• Enfin, double cliquer sur le fichier pour l'ouvrir dans Excel,

Nous débouchons sur la première feuille de ce classeur. Elle est nommée Chercher. En cellule C4, elle suggère effectivement de taper un terme à trouver, partout où il est présent dans le classeur. C'est la raison de la présence du bouton intitulé Trouver. Il est placé sur la droite. A partir de la cellule C6, une grille se suggère elle aussi. Elle est destinée à recevoir les liens hypertextes de recherche, à construire par le code VBA Excel.

De nombreuses autres feuilles composent donc ce classeur. Elles ont fait l'objet d'une formation Excel pour apprendre toutes les subtilités de calculs sur les heures et les dates.

La recherche au clic
C'est donc au clic sur le bouton que le code VBA doit être déclenché. Une procédure lui est déjà attachée et nous proposons de le constater.

• Cliquer avec le bouton droit de la souris sur le bouton,
• En bas du menu contextuel, choisir la commande Affecter une macro,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier,

Nous basculons ainsi dans l'éditeur VBA Excel, entre les bornes de la procédure trouver. Son code se déclenchera au clic sur ce bouton.

Une fois encore et ne l'oublions pas, si nous choisissions de développer ce code VBA dans le modèle Excel, le personal.xlsb, pour l'associer à un bouton de ruban, il serait disponible pour n'importe quel classeur.

Les variables
Pour exécuter ces recherches, nous avons besoin de variables, notamment pour piloter les cellules mais aussi les feuilles.

• Dans la procédure trouver, ajouter les déclarations suivantes, sans les commentaires :

...
'procédure de recherche lancée
Dim bParti As Boolean
'position de la feuille en cours, position de la cellule du résultat inscrit
Dim posF As Integer: Dim posCel As Integer
'Cellule du résultat trouvé et cellule de départ pour les liens
Dim celRes As Range: Dim celDep As Range
Dim texteCherche As String 'Le texte cherché
'Les premières coordonnées trouvées et les suivantes FindNext
Dim adressePrem As String: Dim adresseSuiv As String
'La feuille active et coordonnées potentiellement déjà trouvées pour éviter les doublons
Dim feuille As Worksheet: Dim prec As String
...

Ces commentaires, nous les avons conservés car le code à développer est relativement complexe. Nous déclarons donc et entre autres, des variables pour prélever le texte de recherche, la cellule de départ pour l'inscription des liens, des objets de type Range pour mémoriser les résultats trouvés.

Les initialisations
Puisque les variables utiles sont déclarées, nous pouvons désormais procéder à l'initialisation de certaines, mais pas seulement.

• Après les variables, sans les commentaires, ajouter les instructions VBA suivantes :

...
On Error Resume Next

'On désactive l'actualisation de l'affichage pour ne pas voir les différentes feuilles de recherche
Application.ScreenUpdating = False
Set feuille = ActiveSheet

'Texte cherché
'InputBox de l'objet Application pour pouvoir désigner la cellule à cliquer
texteCherche = Application.InputBox("Cellule du texte à chercher.")
If texteCherche = "" Then Exit Sub
...

Nous désactivons l'actualisation de l'affichage de l'écran pour ne pas le voir basculer d'une feuille à une autre durant le processus de recherche. Nous prenons possession de la feuille active, celle de recherche, en initialisant (Set) l'objet feuille. Nous prélevons l'information sur le texte cherché en récupérant la valeur de la cellule cliquée par l'utilisateur, par le biais d'une boîte de dialogue InputBox. Bien sûr, si (If) la valeur est vide, nous mettons fin au programme (Exit Sub).

Parcourir toutes les feuilles
Nous devons exercer la recherche de la demande dans toutes les feuilles du classeur, à l'exception précisément de celle qui commande ce processus. Donc, nous devons enclencher une boucle les parcourant toutes.

• A la suite du code VBA, créer la boucle For Next suivante :

...
'Boucle pour parcourir toutes les feuilles de la première (1) à la dernière (Count)
For posF = 1 To Worksheets.Count
'S'il s'agit de la feuille en cours, celle de recherche, on l'ignore
If Worksheets(posF).Name <> feuille.Name Then

End If
Next posF
...

Nous engageons la variable posF dans une boucle For Next pour partir de la première feuille et rejoindre la dernière. Cette borne supérieure nous est donnée par la propriété Count de l'objet Worksheets qui représente la collection des feuilles de ce classeur. A chaque passage, nous dégainons une instruction conditionnelle (If) pour nous assurer que le nom de la feuille en cours d'analyse n'est pas celui de la feuille de recherche (feuille.Name). C'est seulement dans ces conditions que nous entendons poursuivre la recherche.

Lancer la recherche
Nous le savons, c'est la méthode Find appliquée sur une plage de cellules, qui permet de lancer la recherche d'un terme ou de mots clés. Nous devons l'appeler pour chaque feuille passée en revue par la boucle.

• Dans l'instruction conditionnelle de la boucle, ajouter les lignes VBA suivantes :

...
'Sur chaque feuille, on prévoit une plage de recherche assez large (A1:Z1000).
'Chaque développeur peut l'adapter
With Worksheets(posF).Range("A1:Z1000")
'Ce que l'on cherche (,,) sous quelle forme (lookIn) (,) à partir (lookAt) (,,,) sans respecter la casse (False)
'Le résultat est stocké dans un range
Set celRes = .Find(texteCherche, , xlValues, xlPart, , , False)
If Not celRes Is Nothing Then 'Si la première recherche est fructueuse

End If
End With
...

Sur chaque feuille, nous prévoyons une plage de cellules assez large (Range("A1:Z1000")) pour être sûr d'étudier toutes les données qu'elle contient. Nous lançons la recherche (Find) du terme (texteCherche) par comparaison de valeurs (xlValues), tout ou partie (xlPart), sans tenir compte de la casse (False). Nous initialisons (Set) l'objet celRes sur la première case répondant favorablement. Si la recherche est concluante (If Not celRes Is Nothing Then), nous poursuivons le traitement.

La plage des liens
Maintenant, nous devons mémoriser la cellule concordante et questionner l'utilisateur, toujours par le biais d'un InputBox, afin de connaître le point départ de la plage à utiliser pour construire les liens de recherche.

• Dans l'instruction conditionnelle précédente, ajouter les lignes VBA suivantes :

...
adressePrem = celRes.Address 'Les coordonnées pour le premier lien
prec = adressePrem 'Mémoriser pour éviter les doublons
If bParti = False Then 'Processus parti - Pour ne pas risquer de redemander la sélection
'On autorise de nouveau l'actualisation pour la boîte de dialogue
Application.ScreenUpdating = True
feuille.Activate
'Pour que la cellule soit prise en tant que telle (coordonnées) il faut spécifier le type, sinon c'est du texte qui est considéré par défaut (Incompatibilité de type)
Set celDep = Application.InputBox("Choisir la cellule à partir de laquelle les liens doivent apparaître.", Type:=8)
Application.ScreenUpdating = False
'La cellule de départ a été sélectionnée. On ne redemande pas.
bParti = True
End If
...

Nous prélevons les coordonnées de la cellule trouvée et les stockons aussi dans une variable tampon (prec), pour vérifier ultérieurement que la demande n'a pas déjà été faite. Nous lançons le processus (If bParti = False Then). Nous réactivons l'affichage pour permettre à l'utilisateur de désigner la première cellule à partir de laquelle il s'agit de construire ces liens de recherche. Nous initialisons (Set) l'objet celDep sur cette sélection de l'utilisateur. Nous désactivons de nouveau l'actualisation de l'affichage pour laisser le processus de recherche se dérouler en toute transparence. Enfin, nous basculons l'état de la variable booléenne bParti pour indiquer que le traitement de recherche est enclenché.

Le premier lien de recherche
Puisque le premier résultat est censé avoir été trouvé et puisque l'utilisateur a désigné le point de départ pour la construction des liens de recherche, nous pouvons commencer par construire le premier.

• A la suite du code précédent, ajouter les instruction VBA suivantes :

...
With feuille
'Reconstruction de l'adresse de destination avec le point d'exclamation pour descendre, depuis la feuille, jusqu'à la cellule cible
adresseSuiv = "'" & Worksheets(posF).Name & "'!" & adressePrem
'Pour ne pas reproposer ce même résultat
prec = adresseSuiv
'Ancre (Coordonnées décalées d'une ligne plus bas par rapport au départ)
'Address = "" car pas de lien externe
'subAddress : le lien vers la cellule du résultat et (,,) le texte à afficher
.Hyperlinks.Add celDep.Offset(posCel, 0),"", adresseSuiv, , adresseSuiv & " (" & celRes.Value & ")"
End With
'On décale le curseur vers le bas pour inscrire le prochain résultat
posCel = posCel + 1
...

Nous réalisons les réglages sur la feuille dans un bloc With pour ne pas répéter le nom de l'objet (feuille) à chaque reprise. Nous construisons l'adresse du résultat dans la variable adresseSuiv. Nous mémorisons cette adresse dans la variable prec, pour la comparer avec les suivantes. Ensuite, nous exploitons la méthode Add de la collection Hyperlinks de l'objet feuille, pour construire le lien hypertexte. En premier argument, nous définissons l'emplacement du lien. Grâce à la méthode Offset, appliquée sur la cellule de départ et à la variable posCel qui va progresser, nous placerons ainsi tous les liens, les uns en-dessous des autres. Nous passons ensuite l'adresse du lien et le texte qu'il doit afficher (Adresse + résultat trouvé).

Poursuivre la recherche jusqu'au dernier
Sur une feuille est comme nous l'avons vu, c'est la méthode Find qui permet de lancer la recherche. C'est ensuite la méthode FindNext qui permet de la poursuivre.

• Toujours à la suite du code VBA, créer la boucle suivante :

...
'On boucle jusqu'à la fin des concordances tout en ignorant les doublons
Do
'On poursuit la recherche sur la même base, soit sur la base de la première concordance
Set celRes = .FindNext(celRes)
'S'il ne s'agit pas du même résultat que le premier
If Len(celRes.Value) > 0 And celRes.Address <> adressePrem Then

End If
'En d'autres termes, tant que la recherche est fructueuse
Loop While Not celRes Is Nothing And celRes.Address <> adressePrem
...

Nous engageons cette boucle (Do) tant qu'un résultat est trouvé (Loop While Not celRes Is Nothing) et que dans le même temps, il ne s'agit pas du même premier résultat. A chaque passage dans cette boucle, nous poursuivons la recherche (FindNext). Si un nouveau résultat est trouvé et qu'il diffère du premier (Len(celRes.Value)> 0 And celRes.Address <> adressePrem), nous poursuivons le traitement.

Construire les liens suivants
A chaque passage dans cette boucle, dès qu'un nouveau résultat est trouvé, nous devons construire le lien suivant, exactement comme nous l'avons fait précédemment.

• Dans l'instruction conditionnelle, créer le bloc With suivant :

...
Do
'On poursuit la recherche sur la même base, soit sur la base de la première concordance
Set celRes = .FindNext(celRes)
'S'il ne s'agit pas du même résultat que le premier
If Len(celRes.Value) > 0 And celRes.Address <> adressePrem Then
With feuille
'On reconstruit la nouvelle adresse
adresseSuiv = "'" & Worksheets(posF).Name & "'!" & celRes.Address
'S'il ne s'agit de la précédente trouvée au niveau des coordonnées
If adresseSuiv <> prec Then
'Ancre(Coordonnées décalées d'une ligne plus bas par rapport au départ)
'Address = "" car pas de lien externe
'subAddress : le lien vers la cellule du résultat et le texte à afficher
.Hyperlinks.Add celDep.Offset(posCel, 0), "", adresseSuiv, ,adresseSuiv & " (" & celRes.Value & ")"
'On mémorise pour ne pas reproduire
prec = adresseSuiv
'On décale vers le bas
posCel = posCel + 1
End If
End With
celRes.Select
End If
'En d'autres termes, tant que la recherche est fructueuse
Loop While Not celRes Is Nothing And celRes.Address <> adressePrem
...

Nous reconstruisons l'adresse du lien comme précédemment. Nous nous assurons qu'il ne s'agit pas d'un résultat déjà trouvé (If adresseSuiv <> prec Then). Nous exploitons de nouveau la méthode Add de la collection Hyperlinks, selon le même protocole. Bien sûr, nous n'oublions pas de mémoriser le nouveau lien pour ne pas le reproduire (prec = adresseSuiv). Enfin, nous incrémentons la variable posCel pour que le prochain lien de recherche soit placé sur la ligne du dessous.

Générer les liens hypertextes
Le code VBA est terminé. Il est temps de le tester.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• En cellule C4, taper un mot clé, comme par exemple : heure,
• Cliquer sur le bouton, désigner cette cellule C4 et valider par Ok,
• A l'invite suivante, désigner la cellule C6 pour définir le point de départ de la construction,

Comme vous pouvez l'apprécier, les liens pointant sur les résultats trouvés, sont automatiquement construits. Si vous cliquez sur l'un ou l'autre d'entre eux, vous êtes automatiquement redirigé sur la cellule de la feuille cible. Si vous changez le terme de recherche, à validation, les nouveaux liens se regénèrent en conséquence. Certes, il convient de vider la zone des résultats préalablement. Il s'agit d'ailleurs d'un petit traitement très facile à entreprendre par le VBA.