rechercher un mot dans une formule

**modus57** · 04/01/2009, 14h31

Bonjour le forum

Comment rechercher la valeur "=RECHERCHE " dans toutes les cellules contenant une formule de la colonne A de la feuille de calcul active

J’ai essayé avec le code ci-dessous, çà fonctionne pas ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
On Error Resume Next
    For Each c In Activesheet.Range("A:A").SpecialCells(xlFormulas)
        If c.Formula Like "*=RECHERCHE*" Then
            MsgBox c.Address
        Else
            MsgBox "Aucune formule dans les cellules de la colonne A"
        End If
    Next

J’ai besoin de votre aide svp

Merci d’avance

**Pierre Fauconnier** · 04/01/2009, 15h21

Bonjour

Formula renvoie la formule en anglais
Essaie avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if c.formula like "=LOOKUP*"

Si tu veux rechercher dans la formule en français, essaie

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if c.formulalocal like "=RECHERCHE*"

mais cela t'expose à des problèmes si ton classeur doit fonctionner également sur des versions non françaises...

**modus57** · 04/01/2009, 16h14

Bonjour Pierre Fauconnier

J'ai testé avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FormulaLocal

à prime abord çà marche

Je fais encore quelques tests, si c'est bon je marque "résolue" pour cette discussion

pour ta réponse

**modus57** · 04/01/2009, 16h39

Re bonjour Pierre Fauconnier

A la première exécution de la macro çà marche, mais quand j’ajoute dans une nouvelle cellule la formule « RECHERCHE » la macro ne marche plus. Je ferme alors le fichier et je l’ouvre à nouveau, la macro ne marche pas.

Avez-vous une explication ?

A+

**Pierre Fauconnier** · 04/01/2009, 17h16

Il faut t'assurer de la chaine que tu recherches...

Si tu recherches =RECHERCHE* ou *=RECHERCHE*, tu n'auras que les formules qui commencent par la chaine cherchée. Si tu cherches également les formules contenant RECHERCHE mais pas uniquement au début, tu dois utiliser

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

c.formulalocal like "*RECHERCHE*"

.

Pourrais-tu donner ton code et des exemples de formules saisies dans Excel?

**modus57** · 04/01/2009, 18h28

Bonsoir

Je joins un petit fichier pour mieux expliquer le problème.

A +

**Pierre Fauconnier** · 04/01/2009, 18h53

C'est normal. Tu places ton IF... à l'intérieur de la boucle => si la première cellule de la plage ne contient pas RECHERCHE, tu passes sur ELSE puis tu sors de la boucle.

Voici une macro qui réalise ce que tu souhaites, en une seule procédure (sans tes deux procédures appelées avec CALL)...

Regarde comment je gère le test et comment j'ai procédé pour modifier la formule. J'ai préféré utiliser Formula plutôt que FormulaLocal pour des questions de compatibilité. De plus, j'ai évité le c.Select car il est inutile et ralentit le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub RechercheRemplaceFormule()
    Dim c As Range
    Dim CelluleAvecRecherche As Boolean
    Dim Formule As String
 
    For Each c In Sheets(1).Range("A:A").SpecialCells(xlFormulas)
        If c.Formula Like "=LOOKUP*" Then
            CelluleAvecRecherche = True
            Formule = Replace(Split(c.Formula, ",")(2), ")", "")
            Formule = "=INDEX(" & Formule & ",MATCH($A$2,Feuil3!$A:$A,0))"
            c.Formula = Formule
        End If
    Next
 
    If Not CelluleAvecRecherche Then MsgBox "Pas de formule avec RECHERCHE"
End Sub

**modus57** · 04/01/2009, 19h29

re bonsoir

Comme tu as pus le constater j'ai bidouiller mon code avec les moyens du bord pour trouver une solution pas très élégante a mon problème !

Par contre ton code c'est pas de la marche c'est du sprint, il est super nickel

Mille fois merci pour ton excellente aide

J'attends encore un peu avant de marquer "résolu" pour la question

**Pierre Fauconnier** · 04/01/2009, 20h05

Tu peux encore accélérer le code.

Si tu es en calcul automatique, passe en manuel. De plus, fige l'écran durant la procédure. Tu gagneras encore quelques miettes, surtout si la plage à scanner est grande.

Ton code sera alors

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub RechercheRemplaceFormule()
    Dim c As Range
    Dim CelluleAvecRecherche As Boolean
    Dim Formule As String
 
    On Error GoTo Fin
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
 
    For Each c In Sheets(1).Range("A:A").SpecialCells(xlFormulas)
        If c.Formula Like "=LOOKUP*" Then
            CelluleAvecRecherche = True
            Formule = Replace(Split(c.Formula, ",")(2), ")", "")
            Formule = "=INDEX(" & Formule & ",MATCH($A$2,Feuil3!$A:$A,0))"
            c.Formula = Formule
        End If
    Next
 
    If Not CelluleAvecRecherche Then MsgBox "Pas de formule avec RECHERCHE"
 
Fin:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox Err.Description, vbOKOnly + vbExclamation, "Erreur " & Err.Number
End Sub

Bonne continuation

**modus57** · 04/01/2009, 22h16

Je suis navré de revenir sur cette discussion
Mais j’ai fait une erreur dans la formulation de ma demande, en fait il, faut transformer la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
RECHERCHE($A$2;Feuil3!$A:$A;Feuil3!$B:$B) en
INDEX(Feuil3!B:B;EQUIV($A$2;Feuil3!$A:$A;0))

Pourrais-tu me dire comment modifier ton code SVP

Merci d’avance pour ton nouveau concours

**Pierre Fauconnier** · 05/01/2009, 18h19

Je ne vois pas où est la modification par rapport aux premières formules

**modus57** · 07/01/2009, 15h16

Bonjour

Au départ la formule est:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

RECHERCHE( $A$2;Feuil3!$A:$A;Feuil3! $B:$B)

Avec ta procédure le résultat obtenu est:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INDEX(Feuil3!$B:$B;EQUIV($A$2;Feuil3!$A:$A;0))

la référence à la colonne $B:$B et la cellule $A$2 sont des références absolues

Et le résultat souhaité est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INDEX(Feuil3!B:B;EQUIV(A2;Feuil3!$A:$A;0))

pour obtenir une référence relative à la colonne B:B et la cellule A2

Merci & A +

**Pierre Fauconnier** · 07/01/2009, 15h19

Remplace la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formule = Replace(Split(c.Formula, ",")(2), ")", "")

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formule = Replace(Replace(Split(c.Formula, ",")(2), ")", ""), "$", "")

Ca devrait aller

**modus57** · 08/01/2009, 16h58

Bonjour

Un point qui m’a échappé dans ton code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formule2 = "=INDEX(" & Formule & ",MATCH($A$2,Feuil3!$A:$A,0))"

Tu récupère en dur MATCH($A$2,Feuil3!$A:$A,0) alors que la cellule $A$2 et Feuil3 peuvent être différentes

Mois j’ai pondu çà,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub RechercheRemplaceFormule()
    Application.ScreenUpdating = False
    On Error Resume Next
    
    For Each c In ActiveSheet.UsedRange.SpecialCells(xlFormulas)
            c.Replace "=", ""
            c.Replace ",", ";"
            c.Replace "LOOKUP", "INDEX(" & Split(c, ";")(UBound(Split(c, ";"))) & ";EQUIV"
            c.Replace ");", ";"
            c.Replace Split(c, ";")(UBound(Split(c, ";"))), "0))"
            c.Replace "$", ""
            c.Replace "A:A", "$A:$A"
            ‘c.Replace "INDEX", "=INDEX" ‘ne fonctionne pas
    Next
    Application.ScreenUpdating = True
    
End Sub

Pourquoi c.Replace "INDEX", "=INDEX" ne fonctionne pas

**Pierre Fauconnier** · 08/01/2009, 17h09

J'ai mis ces références en dur en me basant sur ton fichier, dans lequel c'est dans la cellule A2 que tu saisis la référence qui sert de recherche sur la zone en feuille 3. Si ces références devaient changer, je préfèrerais travailler alors avec des plages nommées, ainsi, ma macro reste simple et lisible et je délègue à Excel le soin de gérer les plages nommées.

Pourquoi c.replace "index", "=index" ne fonctionne pas?

Parce qu'il faut passer par la propriété "formula". C'est pourquoi j'utilise une variable string pour créer la formule et que je passe par la propriété Formula plutôt que par la méthode replace de l'objet range

**modus57** · 10/01/2009, 09h22

Bonjour

J'ai modifier le code comme suit, en rapport avec ma dernière question

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
For Each c In ActiveSheet.UsedRange.SpecialCells(xlFormulas)
        If c.Formula Like "=LOOKUP*" Then
            CelluleAvecRecherche = True
            Formule = Replace(Replace(Split(c.Formula, ",")(2), ")", ""), "$", "")
            Formule1 = Mid(c.Formula, InStr(c.Formula, "(") + 1, InStr(c.Formula, ",") - InStr(c.Formula, "(") - 1)
            Formule2 = Replace(Split(c.Formula, ",")(1), ")", "")
            Formule3 = "=INDEX(" & Formule & ",MATCH(" & Formule1 & "," & Formule2 & ",0))"
            c.Formula = Formule3
        End If
    Next

Cela résout mon problème, merci pour ton aide
A moins que tu as une autre idée, si non je marque la discussion comme

.

**Pierre Fauconnier** · 11/01/2009, 11h42

A vue de nez, c'est comme cela que j'aurais procédé.

Bonne continuation!!

rechercher un mot dans une formule

Macros et VBA Excel

Discussions similaires

Partager

Partager