Recherche et modification de formules excel via vba

Version imprimable

19/12/2012, 19h50
beniboy

Recherche et modification de formules excel via vba
Bonsoir,

j'ai créée un tableau comparatif entre 2012 et 2011. Les valeurs de 2011 sont affichées en absolue. Or je souhaite à présent avoir la différence et non pas la valeur absolue par rapport à 2011.
J'ai bcp de feuilles dans plusieurs fichiers sur lesquels je dois appliquer ces changements. Du coup, en VBA, cela sera plus rapide.

Dans ma feuille excel, j'ai une colonne avec en D7 la formule de 2011:

Code:

=INDIRECT.EXT("["&$A$1-1&".xlsm]"&NOMFEUILLE()&"!"&CELLULE("adresse";C7))

cela va me chercher la valeur de 2011 dans un autre fichier.
par rapport à cette formule je souhaite obtenir:

Code:

=(C7-INDIRECT.EXT("["&$A$1-1&".xlsm]"&NOMFEUILLE()&"!"&CELLULE("adresse";C7)))/INDIRECT.EXT("["&$A$1-1&".xlsm]"&NOMFEUILLE()&"!"&CELLULE("adresse";C7))

C7 est la valeur 2012 qui se trouve toujours la cellule à gauche de la laquelle on travaille.

ensuite je le transforme en pourcentage et ajuste les décimaux avec
Code:

1 2 Selection.Style = "Percent" Selection.NumberFormat = "0.00%"
j'ai également une autre formule pour 2011 qui est déjà au format %. Dans la cellule F7 j'ai la formule suivante:

Code:

=INDIRECT.EXT("["&$A$1-1&".xlsm]"&NOMFEUILLE()&"!"&CELLULE("adresse";E7))

sur celle ci je souhaite simplement faire:

Code:

=E7-=INDIRECT.EXT("["&$A$1-1&".xlsm]"&NOMFEUILLE()&"!"&CELLULE("adresse";E7))

E7 est la valeur 2012.

La valeur de 2012 est toujours la cellule dans la colonne à gauche de la cellule que l'on modifie.

J'ai commencé à travailler avec l'enregistreur de macro et le .FIND() mais je bloque complétement.
D un point de vue logique, je pense que le codage va intégrer: find, une condition basée sur le format de la cellule (% ou nombre), DECALER car on utilise la cellule dans la colonne de gauche.

C'est la piste que j'ai pour le moment mais je ne sais pas du tout la mettre en œuvre.
d avance merci !! :ccool:

ps: si plus clair, je peux transmettre un extrait du fichier.

Bonjour,

Voici un exemple de code qui va vous permettre de remplacer la formule qui initialement en D7 vous renvoyait la valeur de l'année précedente par la variation d'une année sur l'autre.

Comme j'imagine que vous avez de nombreuse fois cette formule mais qu'elle est situé dans d'autres cellules que D7 et que par conséquent elle fait référence à d'autres cellules que C7, la recherche (Find) doit chercher un "morceau" de la formule ... ici je choisi de chercher les cellules qui vont contenir "=INDIRECT.EXT" ; une fois que la cellule est trouvée, je fais un remplacement de la formule : formule = (CelluleUneCaseAGauche - Formule)/Formule en prenant soin de supprimer le = de l'ancienne formule (la fonction Mid)

Cette procédure fera les remplacements sur la plage "D1:D50"

Vous refaites le meme genre de code pour le calcul de la différence en F7
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sub RemplacementFormule() Set c = Range("D1:D50").Find(What:="=INDIRECT.EXT", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do c.FormulaR1C1 = "=(RC[-1] - " & Mid(c.FormulaR1C1, 2) & ")/" & Mid(c.FormulaR1C1, 2) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End Sub

bonjour Stef999,

et merci pour votre réponse.
j'ai modifié légèrement votre code et j'ai une "erreur d'execution "13", incompatibilité de type" sur le "set=c" etc.:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sub RemplacementFormule() With Worksheets(1).Range("A6:G37") Set c = .Find(What:="=INDIRECT.EXT", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do c.FormulaR1C1 = "=(RC[-1] - " & Mid(c.FormulaR1C1, 2) & ")/" & Mid(c.FormulaR1C1, 2) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With End Sub
en ne gardant que
Code:

1 2 Set c = .Find(What:="=INDIRECT.EXT", LookIn:=xlFormulas, _ MatchCase:=False, SearchFormat:=False)
j'ai une erreur sur le LOOP mais les changements s effectuent dans la feuille pour toutes les cellules correctement.

j'ai quelques questions supplémentaires:

est il possible par ailleurs d'appliquer le format suivant dans la macro à chaque cellule modifiée:

Style = "Percent"
NumberFormat = "0.00%"
si valeur>0 then texte en VERT
si valeur<0 then texte en ROUGE

la macro peut elle etre appliquee à plusieurs zones distinctes, qqc comme:

Code:

With Worksheets(1).Range("A6:G37") &.range("O6:Q37")

j'espère avoir été clair. merci encore pour les réponses. :)
a+

20/12/2012, 13h06
Stef999

Bonjour,

Je n'avais pas testé le code ... il y avait peut être des erreurs ...
utilisez pour faire un "With Worksheets(1).Range("A6:G37") &.range("O6:Q37")"

Code:

With Worksheets(1).Range("A6:G37,O6:Q37")

Pour la couleur, faites un test (If xxx>0 then etc ...)

Pour le format : range("xxx").NumberFormat = "0.00%"

bonjour,

merci pour toutes ces infos. j'ai bien avancé du coup.
il me reste un petit problème à présent.

J'ai 2 zones distinctes où mes formules sont modifiés (définies dans mon code ci dessous). Je dois à présent appliquer cette procédure à l ensemble des feuilles de mon classeur à l'exception de celles nommées "listes" et "dashboard".
Quand je lance la macro, j'ai un debogage à chaque fois sur

Code:

With Range("L7:L9,L12:L22,L26:L37,N7:N9,N12:N22,N26:N37,W7:W9,W12:W22,W26:W37,Y7:Y9,Y12:Y22,Y26:Y37")

Code:

With Range("P7:P9,P12:P22,P26:P37,R7:R9,R12:R22,R26:R37,AA7:AA9,AA12:AA22,AA26:AA37,AC7:AC9,AC12:AC22,AC26:AC37")

du coup, cela ne marche pas correctement. le message est:
erreur d execution 1004
microsoft excel ne trouve pas les données que vous recherchez

quand je passe la souris sur les codes en debogage, cela m indique:
la methode RANGE de l objet GLOBAL a échoué.

voila mon code.
merci pour le coup de main encore.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
'changement formule pour % difference avec année N-1 + format conditionnel + format %
Sub RemplacementFormuleongletmois()
Dim c As Range
Dim d As Range
Dim firstAddress As String
Dim dfirstAddress As String
 
Dim fl As Worksheet
For Each fl In Worksheets
  If fl.Name <> "dashboard" And fl.Name <> "listes" Then 'attention aux noms de feuilles avec accent
  'ton code
 
 With Range("L7:L9,L12:L22,L26:L37,N7:N9,N12:N22,N26:N37,W7:W9,W12:W22,W26:W37,Y7:Y9,Y12:Y22,Y26:Y37")
    Set c = .Find(What:="=INDIRECT.EXT", LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
 
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.FormulaR1C1 = "=(RC[-1] - " & Mid(c.FormulaR1C1, 2) & ")/" & Mid(c.FormulaR1C1, 2)
            .NumberFormat = "0.00%"
 
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
            Set c = .FindNext(c)
        Loop While Not c Is Nothing 'And c.Address <> firstAddress
    End If
 
 
  With Range("P7:P9,P12:P22,P26:P37,R7:R9,R12:R22,R26:R37,AA7:AA9,AA12:AA22,AA26:AA37,AC7:AC9,AC12:AC22,AC26:AC37")
    Set d = .Find(What:="=INDIRECT.EXT", LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
 
    If Not d Is Nothing Then
        dfirstAddress = d.Address
        Do
            d.FormulaR1C1 = "=(RC[-1] - " & Mid(d.FormulaR1C1, 2) & ")"
            .NumberFormat = "0.00%"
 
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
            Set d = .FindNext(d)
        Loop While Not d Is Nothing 'And dfirstAddress <> d.Address
    End If
 End With
 End With
  End If
Next fl
 
 
End Sub