rechercher un mot dans une formule

Version imprimable

Bonjour le forum :D

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:

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

04/01/2009, 15h21
Pierre Fauconnier

Bonjour

Formula renvoie la formule en anglais
Essaie avec

Code:

if c.formula like "=LOOKUP*"

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

Code:

if c.formulalocal like "=RECHERCHE*"

mais cela t'expose à des problèmes si ton classeur doit fonctionner également sur des versions non françaises...
04/01/2009, 16h14
modus57

Bonjour Pierre Fauconnier

J'ai testé avec

Code:

FormulaLocal

à prime abord çà marche

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

:merci: pour ta réponse
04/01/2009, 16h39
modus57

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. :roll:
Avez-vous une explication ?

A+
04/01/2009, 17h16
Pierre Fauconnier

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:

c.formulalocal like "*RECHERCHE*"

.

Pourrais-tu donner ton code et des exemples de formules saisies dans Excel?
04/01/2009, 18h28
modus57

1 pièce(s) jointe(s)

Bonsoir

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

A +

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:

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

04/01/2009, 19h29
modus57

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 :yaisse2::king:

Mille fois merci pour ton excellente aide

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

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:

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

04/01/2009, 22h16
modus57
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:

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
05/01/2009, 18h19
Pierre Fauconnier

Je ne vois pas où est la modification par rapport aux premières formules
07/01/2009, 15h16
modus57

Bonjour

Au départ la formule est:

Code:

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

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

Code:

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:

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 +
07/01/2009, 15h19
Pierre Fauconnier

Remplace la ligne

Code:

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

par

Code:

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

Ca devrait aller

Bonjour :)

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

Code:

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:

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 :?

08/01/2009, 17h09
Pierre Fauconnier

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

Bonjour

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

Code:

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 :resolu:.

11/01/2009, 11h42
Pierre Fauconnier

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

Bonne continuation!!