Discussion :

[jeandani2012]

Bonjour,

j'ai codé une foonction à l'aide d'une structure select case en deux blocs

cependant seul la première partie du bloc s'execute correctement.
le second ne s'execute pa


en fait comme je disais le la seconde partie du code ne ramène pas les valeurs souhaitées:

voici le code

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
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 + Cel2) * p
 
Select Case cel3
 
 Case 993
 Case 997
 
 Fonction = (Cel1 + Cel2) * 0
 End Select
 
End Function

s'il vous plait quelqu'un a-t-il la réponse ou avez vous eu à faire un cas similaire ?

Merci d'avance pour vos réponses.

Cordialement.

[Kimy_Ire]

Sort "Fonction = (Cel1 + Cel2) * 0" et donne des consignes à tes cases de ton second select.

D'ailleurs, je ne comprends pas bien ce que tu veux faire... Tu as deux fois "Fonctions" ?

[patricktoulon]

bonsoir

une petite erreur toute bete

tu a dans ta fonction des variable "range"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Function Fonction(CelPourcent As Range, Cel1 As Range, Cel2 As Range, cel3 As Range)

et tu a en bas de ta fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Select Case cel3
 
 Case 993
 Case 997

case 993 et 997 c'est quoi leur valeur??

essai pluto ca :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Select Case cel3.value
 
 Case 993
 Case 997

et c'est pareil pour les "celspercent" select case celpercent.valueau plaisir

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
Function Fonction(CelPourcent As Range, Cel1 As Range, Cel2 As Range, cel3 As Range) As Long
 
Dim p As Long
 
 
 
 
Select Case CelPourcent.Value
 
 
     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 + Cel2) * p
 Select Case cel3.Value
 
 Case 993, 997
 
 
 Fonction = (Cel1 + Cel2) * 0
 
 
 End Select
 
End Function

[jeandani2012]

bonjour merci d'avoir réagi si vite

oui en effet 933 ET 997 sont des valeurs je modifier le code et il marche comme je veux sauf pour une valeur dans la première partie mais je pense que la raison est ailleurs.

Merci d'avance