[VBA Excel] Problème : Procédure trop grande

Bonjour les amis,

Comme étant débutant sur VBA, je mène un projet qui permettra de gérer un stock avec une centaine de référence.
J'ai essayé de faire les choses comme je les ait compris.
Donc le fichier il essaye de faire la somme des quantités commandés par les clients pour te montrer est ce que t'en a assez de stock en matières premières pour préparer tes commandes, et selon des critères (Le mois d'exécution, la référence choisie par l'utilisateur...).

J'ai réalisé se code qui permettra de faire cela, et sa marche très bien :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Dim Réf As String   Réf = Worksheets("STOCKS").Range("B15").Text   If Worksheets("STOCKS").Range("B15").Text = Réf Then   Worksheets("STOCKS").Range("C18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("C16")) Worksheets("STOCKS").Range("D18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("D16")) Worksheets("STOCKS").Range("E18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("E16")) Worksheets("STOCKS").Range("F18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("F16")) Worksheets("STOCKS").Range("G18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("G16")) Worksheets("STOCKS").Range("H18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("H16")) Worksheets("STOCKS").Range("I18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("I16")) Worksheets("STOCKS").Range("J18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("J16")) Worksheets("STOCKS").Range("K18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("K16")) Worksheets("STOCKS").Range("L18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("L16")) Worksheets("STOCKS").Range("M18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("M16")) Worksheets("STOCKS").Range("N18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("N16")) End If

---

Par la suite, parmi ces centaines de références, j'ai quelques références qui représentent le même produit.
Exemple :

Réf	Quantité
0100	50
0200	20
0110	30

Donc les références 0100 et 0110 représentent le même produit, et quand l'utilisateur choisit la référence 0100 ou bien la 0110, je veux que le programme fait la somme des quantités des deux références (Dans notre exemple faut avoir le résultat suivant : 80)
J'ai réalisé un code qui permet de faire cela mais je l'ai fait pour chaque cas, sachant que j'en ai 16 cas différents en plus du cas que j'ai mis juste avant.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

If Worksheets("STOCKS").Range("B15").Text = "0100" Or Worksheets("STOCKS").Range("B15").Text = "0110" Then Worksheets("STOCKS").Range("C18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("C16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("C16")) Worksheets("STOCKS").Range("D18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("D16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("D16")) Worksheets("STOCKS").Range("E18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("E16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("E16")) Worksheets("STOCKS").Range("F18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("F16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("F16")) Worksheets("STOCKS").Range("G18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("G16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("G16")) Worksheets("STOCKS").Range("H18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("H16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("H16")) Worksheets("STOCKS").Range("I18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("I16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("I16")) Worksheets("STOCKS").Range("J18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("J16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("J16")) Worksheets("STOCKS").Range("K18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("K16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("K16")) Worksheets("STOCKS").Range("L18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("L16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("L16")) Worksheets("STOCKS").Range("M18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("M16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("M16")) Worksheets("STOCKS").Range("N18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0100", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("N16")) + Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), "0110", Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("N16")) End If

---

Comment puis-je faire pour diminuer de mon programme pour qu'il ne soit pas lourd.

Je vous remercie.

Bonjour,

Plutôt que range("C18") tu peux écrire cells(18,3) et donc boucler pour remplacer:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Worksheets("STOCKS").Range("C18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("C16")) Worksheets("STOCKS").Range("D18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("D16")) Worksheets("STOCKS").Range("E18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("E16")) Worksheets("STOCKS").Range("F18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("F16")) Worksheets("STOCKS").Range("G18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("G16")) Worksheets("STOCKS").Range("H18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("H16")) Worksheets("STOCKS").Range("I18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("I16")) Worksheets("STOCKS").Range("J18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("J16")) Worksheets("STOCKS").Range("K18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("K16")) Worksheets("STOCKS").Range("L18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("L16")) Worksheets("STOCKS").Range("M18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("M16")) Worksheets("STOCKS").Range("N18").Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").Range("N16"))

---

par

Code:

---

1 2 3

for i = 3 to 14 'pas sur du 14 flemme de compter Worksheets("STOCKS").cells(18,i).Value = Application.WorksheetFunction.SumIfs(Worksheets("COMMANDES").Range("J11:J65535"), Worksheets("COMMANDES").Range("B11:B65535"), Worksheets("STOCKS").Range("B15"), Worksheets("COMMANDES").Range("L11:L65535"), Worksheets("STOCKS").cells(16,i)) next i

---

Bonjour
Utiliser des "blocs with" (lire la rubrique With, instruction dans ton aide interne VBA)
ET
utiliser des procédures paramétrées, appelables en tant que de besoin

Merci beaucoup, ça marche nickel.
Je clôture la discussion.

Salut.

Il y a déjà moyen de faire pas mal de choses sans une ligne de code en utilisant:

• les tableaux structurés^(*);
• des tables de correspondances pour regrouper les références différentes pointant sur un même article;
• les tableaux croisés dynamiques qui vont avec.

Utiliser des fonctions qui prennent qui plongent sur la ligne 65535 n'est pas une bonne idée.

^(*) Pourrais-tu préciser ta version d'Excel?

Bonjour,

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Public Sub Action() Set Ws1 = Worksheets("STOCKS") Set Ws2 = Worksheets("COMMANDES") Set Plg1 = Worksheets("COMMANDES").Range("J11:J65535") Set Plg2 = Worksheets("COMMANDES").Range("B11:B65535") Set Plg3 = Worksheets("COMMANDES").Range("L11:L65535") If Worksheets("STOCKS").Range("B15").Text = "0100" Or Worksheets("STOCKS").Range("B15").Text = "0110" Then For i = 3 To 14 Ws1.Cells(18, i).Value = Application.WorksheetFunction.SumIfs(Plg1, Plg2, Ws1.Range("B15"), Plg3, Ws1.Cells(16, i)) Next i End If End Sub

---

@+JP

Bonjour,

et ne pas oublier de libérer toute variable objet en fin de procédure via Nothing !

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …