Bonjour à tous,

Je ne sais pas comment simplifier cette petite macro, alors je suis obligé de tester par lot de variables. Si quelqu'un avait une solution !!!

Merci par avance

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
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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
Option Explicit
 
Sub SolutionCombinatoire()
Dim strFormuleA As String
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, g As Long, h As Long, i As Long
Application.ScreenUpdating = False
 
Range("X2:Z1000").ClearContents
 
'avec 2 variables '''''''''''''''''''''''''''''''''''''''''''''
    For a = -12 To -4
        For b = a + 1 To -3
 
    strFormuleA = "=RC[" & a & "]+RC[" & b & "]"
    Range("M2:M1401").FormulaR1C1 = strFormuleA
 
Range("X1000").End(xlUp)(2).Resize(1, 3).Value = Range("X1:Z1").Value
 
        Next
    Next
 
'avec 3 variables'''''''''''''''''''''''''''''''''''''''''''
    For a = -12 To -5
        For b = a + 1 To -4
            For c = b + 1 To -3
 
    strFormuleA = "=RC[" & a & "]+RC[" & b & "]+RC[" & c & "]"
    Range("M2:M1401").FormulaR1C1 = strFormuleA
 
Range("X1000").End(xlUp)(2).Resize(1, 3).Value = Range("X1:Z1").Value
 
 
            Next
        Next
    Next
 
'avec 4 variables'''''''''''''''''''''''''''''''''''''''''''''''''''''
    For a = -12 To -6
        For b = a + 1 To -5
            For c = b + 1 To -4
                For d = c + 1 To -3
 
    strFormuleA = "=RC[" & a & "]+RC[" & b & "]+RC[" & c & "]+RC[" & d & "]"
    Range("M2:M1401").FormulaR1C1 = strFormuleA
 
Range("X1000").End(xlUp)(2).Resize(1, 3).Value = Range("X1:Z1").Value
 
 
                Next
            Next
        Next
    Next
 
'avec 5 variables'''''''''''''''''''''''''''''''''''''''''''''''''
 
    For a = -12 To -7
        For b = a + 1 To -6
            For c = b + 1 To -5
                For d = c + 1 To -4
                    For e = d + 1 To -3
 
 
    strFormuleA = "=RC[" & a & "]+RC[" & b & "]+RC[" & c & "]+RC[" & d & "]+RC[" & e & "]"
    Range("M2:M1401").FormulaR1C1 = strFormuleA
 
Range("X1000").End(xlUp)(2).Resize(1, 3).Value = Range("X1:Z1").Value
 
 
                    Next
                Next
            Next
        Next
    Next
 
'avec 6 variables'''''''''''''''''''''''''''''''''''''''''''''''''
    For a = -12 To -8
        For b = a + 1 To -7
            For c = b + 1 To -6
                For d = c + 1 To -5
                    For e = d + 1 To -4
                        For f = e + 1 To -3
 
    strFormuleA = "=RC[" & a & "]+RC[" & b & "]+RC[" & c & "]+RC[" & d & "]+RC[" & e & "]+RC[" & f & "]"
    Range("M2:M1401").FormulaR1C1 = strFormuleA
 
Range("X1000").End(xlUp)(2).Resize(1, 3).Value = Range("X1:Z1").Value
 
 
                        Next
                    Next
                Next
            Next
        Next
    Next
 
'avec 7 variables'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For a = -12 To -9
        For b = a + 1 To -8
            For c = b + 1 To -7
                For d = c + 1 To -6
                    For e = d + 1 To -5
                        For f = e + 1 To -4
                            For g = f + 1 To -3
 
    strFormuleA = "=RC[" & a & "]+RC[" & b & "]+RC[" & c & "]+RC[" & d & "]+RC[" & e & "]+RC[" & f & "]+RC[" & g & "]"
    Range("M2:M1401").FormulaR1C1 = strFormuleA
 
Range("X1000").End(xlUp)(2).Resize(1, 3).Value = Range("X1:Z1").Value
 
                            Next
                        Next
                    Next
                Next
            Next
        Next
    Next
 
ActiveWorkbook.Save
Application.ScreenUpdating = True
 
End Sub