Nomenclature multi-niveau SUMIF

**crypnosis** · 16/12/2017, 22h30

Bonsoir à tous et merci d'avance de lire mon post,

D'habitude je m'en sors très bien avec toutes les astuces VBA excel que je trouve sur votre site et bien d'autres, excel étant sans limite :-)

Mais là, je fais appel à votre aide pour la première fois car je me casse les dents depuis plusieurs jours sur un problème "simple" que la communauté devrait pouvoir résoudre (je l'espère)

Voici mon soucis, j'ai une nomenclature ou BOM à plusieurs niveau, que je détermine avec un combobox, ici pour exemple 1,2,3 en colonne A et j'ai des prix en colonne I

Je souhaite faire un SUMIF dans chaque niveau 1 des niveaux 2, normal dans une BOM vous allez me dire....

Mon problème est que l'expression "application.sumif" marche très bien, mais ce que je souhaite c'est écrire la formule en "dur" dans la cellule déterminée "solution".

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
Sub bouclesumif()
 
Dim z, f, crit, crit2 As Integer
 
Dim qa, qb, qt, qs, solution, niveau, prix As Range
 
f = 2
z = f + 1
 
Set qa = Cells(f, 1)
Set qb = Cells(z, 1)
 
crit = 1
crit2 = 2
 
While qa <> ""
 
If qa.Value = crit Then
 
While qb.Value <> crit Or qb.Value = ""
 
If qb.Value = crit Then
 
Else: z = z + 1
 
Set qb = Cells(z, 1)
 
End If
 
Wend
 
Set qa = Cells(f, 1)
Set qb = Cells(z, 1)
 
Set qt = Cells(f, 9)
Set qs = Cells(z, 9)
 
Set niveau = Range(qa, qb)
Set prix = Range(qt, qs)
 
Set solution = Cells(f, 15)
 
solution.Select
 
ActiveCell = Application.SumIf(niveau, crit2, prix)
 
'ActiveCell.FormulaR1C1 = "=sumif(" & Range(qa, qb) & ", " & crit2 & ", " & Range(qt, qs) & ")"
 
z = z + 1
 
Set qb = Cells(z, 1)
 
End If
 
f = f + 1
Set qa = Cells(f, 1)
 
Wend
 
End Sub

Si l'un d'entre vous avait une solution, je le remercie d'avance

CRY

**Philippe Tulliez** · 17/12/2017, 01h25

Bonjour,

ce que je souhaite c'est écrire la formule en "dur" dans la cellule déterminée "solution[
Si l'un d'entre vous avait une solution, je le remercie d'avance

La lecture de ces deux billets Ecrire une formule dans Excel à l'aide d'une procédure VBA et sa suite Comment écrire une formule plus complexe dans une cellule ? t'aidera certainement à écrire ta formule.

**crypnosis** · 17/12/2017, 12h29

Bonjour Philippe et un grand merci pour ton billet ;-)

Voici le nouveau code qui fonctionne pour écrire la formule dans la cellule :

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
 
 
Sub bouclesumif()
 
Dim z, f, crit As Integer
 
Dim qa, qb, qt, qs, solution, niveau, prix As Range
 
'initialisation sur la range A2 des niveaux à chercher
 
f = 2
z = f + 1
 
Set qa = Cells(f, 1)
Set qb = Cells(z, 1)
 
'Critère de recherche pour le niveau 1
 
crit = 1
 
'Boucle sur le premier niveau 1 à trouver pour la première adresse du SUMIF
 
While qa <> ""
 
If qa.Value = crit Then
 
'Boucle sur le deuxième niveau 1 à trouver pour la deuxième adresse du SUMIF
 
While qb.Value <> crit Or qb.Value = ""
 
If qb.Value = crit Then
 
Else: z = z + 1
 
Set qb = Cells(z, 1)
 
End If
 
Wend
' fin de la deuxième boucle
 
'Redéfinition de l'adresse des Range qa,qb,qt,qs
Set qa = Cells(f, 1)
Set qb = Cells(z, 1)
 
Set qt = Cells(f, 9)
Set qs = Cells(z, 9)
 
'Compilation de l'adressage Niveau et Prix
 
Set niveau = Range(qa, qb)
Set prix = Range(qt, qs)
 
'Définition de la cellule où sera écrit la formule SUMIF
 
Set solution = Cells(f, 15)
 
solution.Select
 
'Réécriture de la formule complexe suivant méthodologie de Philippe Tulliez - www.developpez.net
 
Const myformula As String = "=SUMIF(niveau,crit2,prix)"
Dim crit2 As String
crit2 = 2
Dim newformula As String
 
newformula = Replace(myformula, "niveau", niveau.Address)
newformula = Replace(newformula, "crit2", crit2)
newformula = Replace(newformula, "prix", prix.Address)
solution.Formula = newformula
 
'Boucle pour chercher les niveau 1 suivant
 
z = z + 1
 
Set qb = Cells(z, 1)
 
End If
 
f = f + 1
Set qa = Cells(f, 1)
 
Wend
 
'fin de la première boucle
 
End Sub

J'ai encore un petit bug à la fin dans ma boucle, lorsque la range "qb" ne trouve plus de niveau 1, elle devrait s'arrêter à la dernière cellule non vide,

Je continue de chercher et je vous transmets le code dès qu'il est aboutie.

Merci encore

CRY

**Philippe Tulliez** · 17/12/2017, 13h51

Bonjour,
Je ne vois pas la ligne où a lieu l'erreur mais en tout les cas, je vois un problème qui bien entendu n'est pas responsable de cette erreur au ligne 5 et 7. La ligne déclare bien les variables mais les dernières variables seules sont typées
Dans une ligne telle que Dim a, b, c as Integer la variable c est typée comme Integer, les autres sont de type Variant

**crypnosis** · 17/12/2017, 14h27

Yes !!! Manquait juste une condition "AND" pour que la boucle WHILE sur "qb" s'arrête et fasse le job dans la formule SUMIF...

Ancienne ligne

Code :

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

1
2
 
While qb.Value <> crit

Nouvelle ligne

Code :

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

1
2
 
While qb.Value <> crit And qb.Value <> ""

Le code complet :

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
 
Sub bouclesumif()
 
Dim z, f, crit As Integer
 
Dim qa, qb, qt, qs, solution, niveau, prix As Range
 
'initialisation sur la range A2 des niveaux à chercher
 
f = 2
z = f + 1
 
Set qa = Cells(f, 1)
Set qb = Cells(z, 1)
 
'Critère de recherche pour le niveau 1
 
crit = 1
 
'Boucle sur le premier niveau 1 à trouver pour la première adresse du SUMIF
 
While qa.Value <> ""
 
If qa.Value = crit Then
 
'Boucle sur le deuxième niveau 1 à trouver pour la deuxième adresse du SUMIF
 
While qb.Value <> crit And qb.Value <> ""
 
If qb.Value = crit Or qb.Value = "" Then
 
Else: z = z + 1
 
Set qb = Cells(z, 1)
 
End If
 
Wend
' fin de la deuxième boucle
 
'Redéfinition de l'adresse des Range qa,qb,qt,qs
Set qa = Cells(f, 1)
Set qb = Cells(z, 1)
 
Set qt = Cells(f, 9)
Set qs = Cells(z, 9)
 
'Compilation de l'adressage Niveau et Prix
 
Set niveau = Range(qa, qb)
Set prix = Range(qt, qs)
 
'Définition de la cellule où sera écrit la formule SUMIF
 
Set solution = Cells(f, 15)
 
'solution.Select
 
'Réécriture de la formule complexe suivant méthodologie de Philippe Tulliez - www.developpez.net
 
Const myformula As String = "=SUMIF(niveau,crit2,prix)"
Dim crit2 As String
crit2 = 2
Dim newformula As String
 
newformula = Replace(myformula, "niveau", niveau.Address)
newformula = Replace(newformula, "crit2", crit2)
newformula = Replace(newformula, "prix", prix.Address)
solution.Formula = newformula
 
'Boucle pour chercher les niveau 1 suivant
 
z = z + 1
 
Set qb = Cells(z, 1)
 
End If
 
f = f + 1
Set qa = Cells(f, 1)
 
Wend
 
'fin de la première boucle
 
End Sub

Merci particulier à Philippe pour son aide

CRY

**crypnosis** · 17/12/2017, 14h34

Pardon Philippe j'ai poster sans voir ta réponse,

Pour améliorer le code tu veux dire que sur la ligne z, f, crit as integer, seule la variable "crit" est déclaré integer et les autres en variant ?

Merci

CRY

**Philippe Tulliez** · 17/12/2017, 14h38

Bonjour,

Pour améliorer le code tu veux dire que sur la ligne z, f, crit as integer, seule la variable "crit" est déclaré integer et les autres en variant ?

Oui, c'est exactement ce que j'ai écrit

Dans une ligne telle que Dim a, b, c as Integer la variable c est typée comme Integer, les autres sont de type Variant

A lire éventuellement Utiliser les variables en VBA Excel

**crypnosis** · 17/12/2017, 18h50

Merci Philippe pour tes conseils !

**valentin18F** · 02/08/2018, 14h56

Bonjour Crypnosis,

je vais devoir commencer à bosser sous peu sur l' automatisation de bons commandes sur des produits qui sont liés entre eux via une nomenclature, en fouinant sur internet à ce sujet je suis tombé sur ton post, j' aurais donc simplement voulu quel est ton point départ et de quelle manière concrètement tu as modélisé ta nomenclature, tu as évoqué le combobox, comment cela se traduit - il ?

merci d' avance pour ta réponse

Valentin

Nomenclature multi-niveau SUMIF [XL-2016]

Excel

Discussions similaires

Partager

Partager