Automatisation ou actualisation Formule

Version imprimable

07/05/2012, 11h31
jackborogar

Automatisation ou actualisation Formule

Bonjour à tous et à toute,

Avant d'expliquer mon problème, je vais vous dessiner avec des mots l'architecture de mon Excel.
Il y a tout d'abord une page où l'on peut ajouter ou supprimer un tableau via des boutons. Ces tableaux représentent réellement des usines donc on appelera TableauUsine.
Une autre feuille comporte un tableau récapitulatif "TableauRecap" , de tous mes TableauxUsine.
Cependant ce système n'existait pas avant et donc il fallait tout faire à la main. Même les formules et c'est là qu'intervient la subtilité du problème.

Voici un exemple de calcul en B203 du TableauRecap :

Code:

=(SOMMEPROD(B9:B20;C9:C20)+SOMMEPROD(B29:B40;C29:C40)+SOMMEPROD(B50:B61;C50:C61)+SOMMEPROD(B70:B81;C70:C81)+SOMMEPROD(B90:B101;C90:C101)+SOMMEPROD(B147:B158;C147:C158)+SOMMEPROD(B166:B177;C166:C177))/SOMME(B22;B42;B63;B83;B103;B160;B179)

Or si j'ajoute un tableauUsine j'aimerai que la formule prenne en compte cette ajout dans la formule.

Alors ma question est simple, est ce possible d'automatiser la formule? Et si oui quelle direction faut-il que je prenne?

En vous remerciant d'avance,

Cordialement,
07/05/2012, 11h39
redstoff

Bonjour,

Peux tu mettre, stp, un exemple de ta formule avec une usine en plus ou en moins.
Ca permettrait de bien voir la difference ^^

merci

[Edit]
ok je pense a cerné ta formule^^ j'y consacre un peu de temps pour voir si j'y trouve quelque chose ^^
07/05/2012, 11h45
jackborogar

Bonjour à tous et à toute,

Avant d'expliquer mon problème, je vais vous dessiner avec des mots l'architecture de mon Excel.
Il y a tout d'abord une page où l'on peut ajouter ou supprimer un tableau via des boutons. Ces tableaux représentent réellement des usines donc on appelera TableauUsine.
Une autre feuille comporte un tableau récapitulatif "TableauRecap" , de tous mes TableauxUsine.
Cependant ce système n'existait pas avant et donc il fallait tout faire à la main. Même les formules et c'est là qu'intervient la subtilité du problème.

Voici un exemple de calcul en B203 du TableauRecap :

Code:

=(SOMMEPROD(B9:B20;C9:C20)+SOMMEPROD(B29:B40;C29:C40)+SOMMEPROD(B50:B61;C50:C61)+SOMMEPROD(B70:B81;C70:C81)+SOMMEPROD(B90:B101;C90:C101)+SOMMEPROD(B147:B158;C147:C158)+SOMMEPROD(B166:B177;C166:C177))/SOMME(B22;B42;B63;B83;B103;B160;B179)

Or si j'ajoute un tableauUsine j'aimerai que la formule prenne en compte cette ajout dans la formule.

Alors ma question est simple, est ce possible d'automatiser la formule? Et si oui quelle direction faut-il que je prenne?

Avec une Usine en plus par exemple :

Code:

=(SOMMEPROD(B9:B20;C9:C20)+SOMMEPROD(B29:B40;C29:C40)+SOMMEPROD(B50:B61;C50:C61)+SOMMEPROD(B70:B81;C70:C81)+SOMMEPROD(B90:B101;C90:C101)+SOMMEPROD(B147:B158;C147:C158)+SOMMEPROD(B166:B177;C166:C177)+SOMMEPROD(B185:B196;C185:C196))/SOMME(B22;B42;B63;B83;B103;B160;B179;B198)

En supprimant l'Usine 2 par exemple:

Code:

=(SOMMEPROD(B9:B20;C9:C20)+<s>SOMMEPROD(B29:B40;C29:C40)</s>+SOMMEPROD(B50:B61;C50:C61)+SOMMEPROD(B70:B81;C70:C81)+SOMMEPROD(B90:B101;C90:C101)+SOMMEPROD(B147:B158;C147:C158)+SOMMEPROD(B166:B177;C166:C177))/SOMME(B22;<s>B42</s>;B63;B83;B103;B160;B179)

Après on peut voir que entre les différents tableaux d'Usines il y a des espaces différents par rapport au SommeProd donc s'il faut que tout les tableaux soient espacés de la même manière il n'y a aucun soucis.

De plus l'ajout d'une Usine ce fait grâce à un copie colle d'une plage représentant mon tableau qui est sur une feuille masquer. Je sais pas si cette info est pertinente ou non.

Cordialement,
07/05/2012, 15h32
fring

Bonjour,

apparemment l'espace entre chaque tableau n'est pas identique, il faudrait donc à mon avis travailler avec des plages nommées pour chaque tableau "usine" et parcourir toutes les plages via une petite macro

bonjour,

En effet il est souhaitable que les tableaux et les écarts entre chacun soient identique

Le code ci dessous devrait te mettre sur la piste. il te restera a trouver la ligne du dernier tableau
Code:

1 2 3 4 5 6 7 8 9 Dim strFormule As String Dim i As Long For i = 10 To 100 Step 20 If strFormule <> "" Then strFormule = strFormule & "+" strFormule = strFormule & "sommeprod(B" & i & ":B" & i + 18 & ";C" & i & ":C" & i + 18 & ")" Next i MsgBox "=" & strFormule

07/05/2012, 16h21
jackborogar

Suis-je bête, cela m'a l'air parfait comme petite macro! Pour la derniere valeur comme je sais pas jusque où ça va s'arrêter je vais le mettre jusqu'à 6000 je serais tranquille non?
Peux tu m'expliquer ton IF?
07/05/2012, 16h42
jfontaine

Code:

If strFormule <> "" Then strFormule = strFormule & "+"

Cette ligne sert a ajouter le signe d'addition pour chaque sommeprod

Je te déconseille de faire jusqu'au la ligne 6000 car ça va alourdir inutilement ton fichier.

La ligne ci dessous retourne la dernière ligne utilisée

Code:

Range("B" & rows.count).end(xlup).row
07/05/2012, 16h45
jackborogar

Nikel, par contre comment appeler mon "Sub Calcul1() " qui se trouve dans le Module4 , pour mettre sa valeur dans une cellule spécifique?

J'ai pourtant fait " = Calcul1 " , sa me marque "Nom Non Valide"
07/05/2012, 17h00
jfontaine

ce code retourne une formule à mettre dans une cellule et non une valeur.

Appel la procédure lors de l'ajout d'une usine

Bonjour,

Alors j'ai pensé écrire mon code ainsi.
Les SommeProd doivent être prendre en compte les chiffres dans la feuille "Achat 2012 - 2013" tandis que le résultat de la formule finale doit être dans la cellule C12 de la feuille Synthèse
Voici ci dessous le code :
Code:

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 Sub Calcul1() Dim strFormule As String strFormule = "SOMMEPROD('Achat 2012-2013'!D14:25;'Achat 2012-2013'!E14:E25)" Dim strFormule2 As String strFormule2 = "'Achat 2012-2013'!D27" Dim i As Long For i = 35 To Range("C" & Rows.Count).End(xlUp).Row Step 21 If Sheet("Achat 2012-2013").Range("B" & i).Values = "Blé" Then strFormule = strFormule & "+" & "SOMMEPROD('Achat 2012-2013'!D" & i & ":D" & i + 11 & ";'Achat 2012-2013'!E" & i & ":E" & i + 11 & ")" End If Next i For i = 48 To Range("C" & Rows.Count).End(xlUp).Row Step 21 If Sheet("Achat 2012-2013").Range("B" & i).Values = "Blé" Then strFormule2 = strFormule2 & "; 'Achat 2012-2013'!D" & i End If Next i strFormule2 = "SOMME(" & strFormule2 & ")" MsgBox "=" & strFormule & "/" & strFormule2 End Sub
J'aimerais savoir quel code faut-il que je mette pour avoir le résultat de mon code ci dessus dans la cellule C12 qui sera un string pour donner au final un resultat sous la forme d'un integer. Code que je metterai dans mon sub qui ajoute une Usine.
Il faut retirer le MsgBox aussi non?

Cordialement,

Bonjour,

Cela devrait ressembler à ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub Calcul1()
Dim strFormule As String, strFormule2 As String, i As Long
 
strFormule = "SOMMEPROD('Achat 2012-2013'!D14:D25;'Achat 2012-2013'!E14:E25)"
strFormule2 = "'Achat 2012-2013'!D27"
 
With Sheets("Achat 2012-2013")
    For i = 35 To .Range("C" & Rows.Count).End(xlUp).Row Step 21
        If .Range("B" & i).Values = "Blé" Then
        strFormule = strFormule & "+" & "SOMMEPROD('Achat 2012-2013'!D" & i & ":D" & i + 11 & ";'Achat 2012-2013'!E" & i & ":E" & i + 11 & ")"
        strFormule2 = strFormule2 & ";'Achat 2012-2013'!D" & i + 13
        End If
    Next i
End With
 
strFormule2 = "SOMME(" & strFormule2 & ")"
 
Sheets("Synthèse").Range("C12") = "=" & strFormule & "/" & strFormule2
 
End Sub

09/05/2012, 14h34
jackborogar

Merci de votre réponse cependant la ligne ci dessous pose un problème qui me fait arrêter mon code:

Code:

Sheets("Synthese").Range("C12") = "=" & strFormule & "/" & strFormule2

Je ne comprends pas trop pourquoi, mais je sais que c'est le strFormule et strFormule2 qui font bugger la chose puisque j'ai essayé avec :

Sheets("Synthese").Range("C12") = "=" & i et j'obtiens bien un résultat.

De plus avec un MsgBox je vérifie bien la cohérence de la formule finale et je ne vois aucun soucis avec ça
09/05/2012, 15h16
fring

Sorry, j'ai oublié de préciser à la cellule cible qu'il s'agit d'une formule

Essaie ceci :

Code:

Sheets("Synthese").Range("C12").Formula = "=" & strFormule & "/" & strFormule2
09/05/2012, 15h27
jackborogar

J'y avait pensé aussi mais non il y a toujours un bug. J'ai essayé moulte chose pour voir d'où peux venir cette erreur et impossible de la trouver
09/05/2012, 15h30
fring

Quel est le message d'erreur ?
Il n'y aurait pas une erreur dans le nom de la feuille ? Synthese --> Synthèse ?
09/05/2012, 15h41
jackborogar

Non non pas de soucis à ce niveau là, j'ai bien pris le temps de le corriger. Je te transmet ci joint le fichier peut être tu y verras plus clair. Je suis déséspérer par ce problème lol

Le code est dans le Module4, et je l'appel via le bouton dans la feuille "achat..." , "Ajouter une usine", et la répercution doit se faire sur "Synthèse" en "C13".

Merci en tout cas de tes réponses et de ton temps passé dessus ,

Bien Cdt
09/05/2012, 16h08
fring

Au temps pour moi, pour utiliser les formules en français, il faut utiliser FormulaLocal et non Formula

Code:

Sheets("Synthèse").Range("C13").FormulaLocal = "=" & strFormule & "/" & strFormule2
09/05/2012, 16h18
jackborogar

Juste ça le problème?
Sa rend un résultat en tout cas, je te remercie, je peux fermer ce topic!

Bonne fin de journée à toi, et encore un grand merci!

A bientôt,

Cdt