Bonjour,

j'ai developpé un code pour calculer plusieurs valeurs en fonction de différents taux:

j'explique:

si la cellule Bi par exemple contient un taux=13.76%
ALORS la cellule AEi se calcule ainsi (Ji+Mi)*22.38% et la cellule BKi=(Ji+Mi)*77.62%

Dans la 1ère partie du code Bi= celpourcent Ji et Mi sont respectivement cel1 et cel2 et p est dans chaque cas le pourcentage par lequel on multiplie Ji+Mi
Ainsi de suite pour les différents taux qui sont enumérés
Dans le seconde partie du code

il ya juste un paramètre de plus pour lequel on a les valeurs 993 ou 997.


Seulement le code tourne très bien pour la plus part des valeurs sauf 3 valeurs qui ne sont pas calculées du ce sont les valeurs 0.074; 0.0912;0.137

j'ai fouillé mais je ne comprends pourquoi. je ne sais pas si quelqu'un a t il eu dejà à faire à ce type de problème ou certaines valeurs ne sont pas calculées et d'autres le sont correctement ?
je précise bien que les fonctions sont séparées les unes des autres ensuite les valeurs qui ne sont pas calculées sont case 0.0137 soit 13.70% en suite le 0.074 soit 7.40% , le 0.0912 soit 9.12%

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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
Function MaFonction(CelPourcent As Range, Cel1 As Range, Cel2 As Range)
Dim p As Single
 
Select Case CelPourcent
    Case 0.113: p = 0.1095
    Case 0.1135: p = 0.1119
    Case 0.114: p = 0.1143
    Case 0.115: p = 0.119
    Case 0.121: p = 0.1476
    Case 0.122: p = 0.1524
    Case 0.127: p = 0.1762
    Case 0.1275: p = 0.1786
    Case 0.128: p = 0.181
    Case 0.1315: p = 0.1976
    Case 0.1326: p = 0.2029
    Case 0.134: p = 0.2095
    Case 0.135: p = 0.2143
    Case 0.137: p = 0.2238
    Case 0.1375: p = 0.2262
    Case 0.1376: p = 0.2267
    Case 0.1389: p = 0.2329
    Case 0.139: p = 0.2333
    Case 0.1391: p = 0.2338
    Case 0.1405: p = 0.2405
    Case 0.1414: p = 0.2448
    Case 0.142: p = 0.2476
    Case 0.1421: p = 0.2481
    Case 0.1432: p = 0.2533
    Case 0.145: p = 0.2619
    Case 0.1453: p = 0.2633
    Case 0.1454: p = 0.2638
    Case 0.1458: p = 0.2657
    Case 0.1468: p = 0.2705
    Case 0.147: p = 0.2714
    Case 0.1473: p = 0.2729
    Case 0.1474: p = 0.2733
    Case 0.1479: p = 0.2757
    Case 0.1485: p = 0.2786
    Case 0.1487: p = 0.2795
    Case 0.1489: p = 0.2805
    Case 0.1492: p = 0.2819
    Case 0.1495: p = 0.2833
    Case 0.15: p = 0.2857
    Case 0.1505: p = 0.2881
    Case 0.1509: p = 0.29
    Case 0.1514: p = 0.2924
    Case 0.155: p = 0.3095
    Case 0.1567: p = 0.3176
    Case 0.158: p = 0.3238
    Case 0.1586: p = 0.3267
    Case 0.1597: p = 0.3319
    Case 0.1609: p = 0.3376
    Case 0.1616: p = 0.341
    Case 0.164: p = 0.3524
    Case 0.165: p = 0.3571
    Case 0.1671: p = 0.3671
    Case 0.169: p = 0.3762
    Case 0.1696: p = 0.379
    Case 0.1716: p = 0.3886
    Case 0.1725: p = 0.3929
    Case 0.173: p = 0.3952
    Case 0.1738: p = 0.399
    Case 0.174: p = 0.4
    Case 0.1894: p = 0.4733
    Case 0.193: p = 0.4905
    Case 0.197: p = 0.5095
    Case 0.201: p = 0.5286
    Case 0.2025: p = 0.5357
    Case 0.21: p = 0.5714
    Case 0.216: p = 0.6
    Case 0.238: p = 0.7048
    Case 0.2494: p = 0.759
    Case 0.257: p = 0.7952
    Case 0.262: p = 0.819
    Case 0.2685: p = 0.85
    Case 0.27: p = 0.8571
    Case 0.2775: p = 0.8929
    Case 0.2832: p = 0.92
    Case 0.3: p = 1
 
 
 
    'etc... autant de conditions que tu veux
End Select
 
MaFonction = (Cel1 + Cel2) * p
 
End Function
Function Fonction(CelPourcent As Range, Cel1 As Range, Cel2 As Range, cel3 As Range)
 
Dim p As Single
 
 
 
 
Select Case CelPourcent
 
 
     Case 0.044: p = 1
     Case 0.45: p = 1
     Case 0.0716: p = 0.08
     Case 0.074: p = 0.2
     Case 0.077: p = 0.35
     Case 0.08: p = 0.5
     Case 0.085: p = 0.75
     Case 0.0852: p = 0.76
     Case 0.09: p = 1
     Case 0.0906: p = 1
     Case 0.0912: p = 1
     Case 0.095: p = 1
     Case 0.102: p = 1
     Case 0.113: p = 0.8905
     Case 0.1135: p = 0.8881
     Case 0.114: p = 0.8857
     Case 0.115: p = 0.881
     Case 0.121: p = 0.8524
     Case 0.122: p = 0.8476
     Case 0.127: p = 0.8238
     Case 0.1275: p = 0.8214
     Case 0.128: p = 0.819
     Case 0.13: p = 0.8095
     Case 0.1315: p = 0.8024
     Case 0.1326: p = 0.7971
     Case 0.134: p = 0.7905
     Case 0.135: p = 0.7857
     Case 0.137: p = 0.7762
     Case 0.1375: p = 0.7738
     Case 0.1376: p = 0.7733
     Case 0.1389: p = 0.7671
     Case 0.139: p = 0.7667
     Case 0.1391: p = 0.7662
     Case 0.1405: p = 0.7595
     Case 0.1414: p = 0.7552
     Case 0.142: p = 0.7524
     Case 0.1421: p = 0.7519
     Case 0.1432: p = 0.7467
     Case 0.145: p = 0.7381
     Case 0.1453: p = 0.7367
     Case 0.1454: p = 0.7362
     Case 0.1458: p = 0.7343
     Case 0.1468: p = 0.7295
     Case 0.147: p = 0.7286
     Case 0.1473: p = 0.7271
     Case 0.1474: p = 0.7267
     Case 0.1479: p = 0.7243
     Case 0.1485: p = 0.7214
     Case 0.1487: p = 0.7205
     Case 0.1489: p = 0.7195
     Case 0.1492: p = 0.7181
     Case 0.1495: p = 0.7167
     Case 0.15: p = 0.7143
     Case 0.1505: p = 0.7119
     Case 0.1509: p = 0.71
     Case 0.1514: p = 0.7076
     Case 0.152: p = 0.7048
     Case 0.1527: p = 1
     Case 0.155: p = 0.6905
     Case 0.1567: p = 0.6824
     Case 0.158: p = 0.6762
     Case 0.1586: p = 0.6733
     Case 0.1597: p = 0.6681
     Case 0.1609: p = 0.6624
     Case 0.1616: p = 0.659
     Case 0.164: p = 0.6476
     Case 0.165: p = 0.6429
     Case 0.1671: p = 0.6329
     Case 0.169: p = 0.6238
     Case 0.1696: p = 0.621
     Case 0.1716: p = 0.6114
     Case 0.1725: p = 0.6071
     Case 0.173: p = 0.6048
     Case 0.1738: p = 0.601
     Case 0.174: p = 0.6
     Case 0.1894: p = 0.5267
     Case 0.193: p = 0.5095
     Case 0.197: p = 0.4905
     Case 0.201: p = 0.4714
     Case 0.2025: p = 0.4643
     Case 0.21: p = 0.4286
     Case 0.216: p = 0.4
     Case 0.238: p = 0.2952
     Case 0.2494: p = 0.241
     Case 0.257: p = 0.2048
     Case 0.262: p = 0.181
     Case 0.2685: p = 0.15
     Case 0.27: p = 0.1429
     Case 0.2775: p = 0.1071
     Case 0.2832: p = 0.08
 
    End Select
 
Fonction = Cel1 * p + Cel2 * p
 
 
 
 Select Case cel3.Value
 
 Case 993, 997
 
 
 Fonction = (Cel1 + Cel2) * 0
 
 
 End Select
 
 
 
End Function
Sub ventilation()
 
Dim i As Integer, ColDep As Integer, ColFin As Integer, LigDep As Integer, DerLig As Integer
 
ColDep = 15 '<-- colonne de départ O
ColFin = 125 '<-- colonne de fin = DU
LigDep = 5 '<-- première ligne
DerLig = Cells(Rows.Count, 1).End(xlUp).Row '<-- dernière ligne
 
Application.ScreenUpdating = False
For i = ColDep To ColFin
    Cells(LigDep, i).AutoFill Destination:=Range(Cells(LigDep, i), Cells(DerLig, i)), Type:=xlFillDefault
Next
 
End Sub