[XL-2007] looping avec RANGE non adjacentes

bonjour,

je tente de modifier le contenu de formules excel. pour cela j'ai 2 modifications dans des zones non adjacentes, par exemple:
modif 1:

Code:

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

modif 2:

Code:

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

j'ai pu comprendre que le probleme vient du fait que les cellules ne sont pas adjacentes. En gardant seulement L7:L9 et P7:P9, mon code marche et tout est modifié correctement.

La solution serait d implementer un LOOP pour chaque zone (soit 1 loop pour L7:L9 puis L12:L22, etc.).

je ne suis pas sur comment intégrer cela dans le code existant.

merci pour le coup de main et les eclaircissements futurs !! :ccool:

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 fl.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 fl.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