Remplacer une formule compliquée par une macro simple

Version imprimable

08/10/2013, 16h52
a.faurie

1 pièce(s) jointe(s)

Remplacer une formule compliquée par une macro simple

Bonjour à tous,

J'ai fait un cadre pour les devis d'entreprises, ne connaissant rien en macro, je n'avais utilisé que des mises en forme conditionnelles et deux formules.

Le problème c'est qu'il n'est pas possible de convertir le fichier en version antérieur...

SVP filer moi un coup de pouce...

Merci d'avance

PS : si au passage il est possible de faire disparaitre la colonne A ça serait super !
08/10/2013, 16h58
EngueEngue

Reviens à noël tu trouveras peut être quelqu'un prêt à faire ton travail avec tes formules indigestes sans aucune explication et en passant des heures à les comprendre. Surtout que l'on n'a aucune idée de la structure des documents.
08/10/2013, 16h59
Marc-L

Bonjour.

Citation:

Envoyé par a.faurie

Le problème c'est qu'il n'est pas possible de convertir le fichier en version antérieur...

Si avec Enregistrer sous en sélectionnant le format Excel 97-2003 …

Sinon sans une présentation claire & exhaustive, ne pas s'attendre à obtenir de l'aide …

Pour rappel : ce forum est là pour aider à résoudre des points précis en particulier,
pas pour surseoir au travail rémunéré de tout à chacun en exploitant les bénévoles de ce forum !

Cordialement.

Quoi marc tu ne sais pas changer cette formule par une macro ?

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
=SI([@niv]=0;[@[QTE
ENT]]*[@[Prix Unitaire € HT]];
 
SI([@niv]=1;
SOMME.SI(INDIRECT(CONCATENER("B";CELLULE("ligne";DPGF1[@])+1)):INDIRECT(CONCATENER("B";
MIN(
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0)+CELLULE("ligne";DPGF1[@])-1;CELLULE("ligne";DPGF1[#Totaux])-1))
));
"";
INDIRECT(CONCATENER("H";CELLULE("ligne";DPGF1[@])+1)):INDIRECT(CONCATENER("H";
MIN(
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1))))
+CELLULE("ligne";DPGF1[@])-1)));
 
SI([@niv]=2;
SOMME.SI(INDIRECT(CONCATENER("B";CELLULE("ligne";DPGF1[@])+1)):INDIRECT(CONCATENER("B";
MIN(
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0)+CELLULE("ligne";DPGF1[@])-1;CELLULE("ligne";DPGF1[#Totaux])-1);
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0)+CELLULE("ligne";DPGF1[@])-1;CELLULE("ligne";DPGF1[#Totaux])-1))
));
"";
INDIRECT(CONCATENER("H";CELLULE("ligne";DPGF1[@])+1)):INDIRECT(CONCATENER("H";
MIN(
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1)));
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1))))
+CELLULE("ligne";DPGF1[@])-1)));
 
SI([@niv]=3;
SOMME.SI(INDIRECT(CONCATENER("B";CELLULE("ligne";DPGF1[@])+1)):INDIRECT(CONCATENER("B";
MIN(
SIERREUR(EQUIV(3;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0)+CELLULE("ligne";DPGF1[@])-1;CELLULE("ligne";DPGF1[#Totaux])-1);
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0)+CELLULE("ligne";DPGF1[@])-1;CELLULE("ligne";DPGF1[#Totaux])-1);
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0)+CELLULE("ligne";DPGF1[@])-1;CELLULE("ligne";DPGF1[#Totaux])-1))
));
"";
INDIRECT(CONCATENER("H";CELLULE("ligne";DPGF1[@])+1)):INDIRECT(CONCATENER("H";
MIN(
SIERREUR(EQUIV(3;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("H";CELLULE("ligne";DPGF1[#Totaux])+1);1)));
SIERREUR(EQUIV(2;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("H";CELLULE("ligne";DPGF1[#Totaux])+1);1)));
SIERREUR(EQUIV(1;INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[@])+1);1):INDIRECT(CONCATENER("A";CELLULE("ligne";DPGF1[#Totaux])+1);1);0);CELLULE("ligne";INDIRECT(CONCATENER("H";CELLULE("ligne";DPGF1[#Totaux])+1);1))))
+CELLULE("ligne";DPGF1[@])-1)));0))))

08/10/2013, 18h20
a.faurie

je vous confirme qu'il n'est pas possible d'enregistrer un fichier 2003 pour plusieurs raisons (nbr caractère trop important, si.erreur non compatible et pas plus de 3 MEC par cellules).

La formule est compliqué afin de ne pas avoir plein de #REF quand je supprime une ligne, si non elle se résume par :

Si REP est vide c'est que c'est une ligne de prix donc :
prix total = quantité * prix unitaire

Si REP pas vide c'est que c'est un sous-total donc :
prix total = somme des prix totaux jusqu'au prochain titre de niveau égal ou supérieur.

mon problème c'est que je ne sais pas du tout comment mi prendre pour faire ça via une macro.

- Comment faire pour que cela se fasse automatiquement sans à avoir lancer la macro à chaque fois ?
- Surtout comment faire pour trouver jusqu'où faire la somme de mes sous-totaux ?
09/10/2013, 18h59
papouclo

1 pièce(s) jointe(s)

Bonjour,
Ce qui pose aussi problème (mais pas de façon irrémédiable) est l'utilisation d'un tableau pour un fichier destiné à une version antérieure à 2007.
J'ai recopié les données du classeur initial sans déclarer de tableau dans le classeur joint.
Je ne réponds pas à la demande puisque je n'ai pas écrit de macro mais des formules (détermination des niveaux en colonne A et des totaux en colonne H)
me semblant un peu plus "digestes" que dans le tableau initial;
mais attention! la formule principale de calcul du prix total HT est une formule "à tiroirs", certaines portions de formules ayant reçu un nom ; voir donc dans le gestionnaire de noms pour avoir une idée complète de la démarche..
Les 3 lignes (lignes 53,54,56) rajoutées avec seulement les niveaux 1, 2, 3 en colonne A permettent de ne pas avoir d'erreur dans la recherche des hauteurs des plages intervenant
dans le calcul des totaux.
Cordialement
Claude
10/10/2013, 10h19
a.faurie

Citation:

Envoyé par papouclo

Bonjour,
Ce qui pose aussi problème (mais pas de façon irrémédiable) est l'utilisation d'un tableau pour un fichier destiné à une version antérieure à 2007.
J'ai recopié les données du classeur initial sans déclarer de tableau dans le classeur joint.
Je ne réponds pas à la demande puisque je n'ai pas écrit de macro mais des formules (détermination des niveaux en colonne A et des totaux en colonne H)
me semblant un peu plus "digestes" que dans le tableau initial;
mais attention! la formule principale de calcul du prix total HT est une formule "à tiroirs", certaines portions de formules ayant reçu un nom ; voir donc dans le gestionnaire de noms pour avoir une idée complète de la démarche..
Les 3 lignes (lignes 53,54,56) rajoutées avec seulement les niveaux 1, 2, 3 en colonne A permettent de ne pas avoir d'erreur dans la recherche des hauteurs des plages intervenant
dans le calcul des totaux.
Cordialement
Claude

A mais carrément c'est trop top merci bcp !
10/10/2013, 17h49
papouclo

Bonjour,
Merci pour l'appréciation !
La solution est-elle satisfaisante ou faut-il toujours réfléchir à une macro ?
Je reviens pour une autre raison :
La formule donnant les niveaux en colonne A est simple mais parce qu'on limite les niveaux au niveau 3; il faudrait la modifier pour des niveaux tels que le niveau 4.
On peut employer en A7 la formule :

Code:

= MOD(NBCAR(B7);2) + ENT(NBCAR(B7)/2)

à recopier vers le bas.
Si l'on utilise la version Excel 2007 ou une version postérieure, on peut écrire :

Code:

= MOD(NBCAR(B7);2) + QUOTIENT(NBCAR(B7);2)

Cordialement
Claude
11/10/2013, 10h19
a.faurie

Bonjour,
Merci pour ton implication.

Pour la formule de la colonne A
Je m'imaginais peut-être changer l'ordre en partant sur cette formule.

Citation:

=SI(DPGF[[#Cette ligne];[REP]]="";0;
SI(DPGF[[#Cette ligne];[REP]]="pm";1;
10-(NBCAR(DPGF[[#Cette ligne];[REP]])-NBCAR(SUBSTITUE(SUBSTITUE(DPGF[[#Cette ligne];[REP]];",";"");".";"")))))

ce qui en 2003 donnerait :

Citation:

=SI($B8="";0;
SI($B8="pm";1;
10-(NBCAR($B8)-NBCAR(SUBSTITUE(SUBSTITUE($B8;",";"");".";"")))))

Franchement si t'es capable de me trouver une macro qui écrirait dans les lignes de prix total les formules suivantes :
=F8*G8 pour les lignes de calculs (ligne ou REP = vide ou
=SOMME.SI(A8:A40;0;H8:H40) pour les lignes de sous-total.
Ça doit faire une bonne semaine que je n'arrive pas à déterminer le A40 qui est la ligne de titre égale ou supérieure
Si tu y arrive c'est parfait dans le cas contraire je renonce.

en tout cas merci.
12/10/2013, 18h47
papouclo

1 pièce(s) jointe(s)

Bonjour,
Regarde si la version du classeur joint te convient.
Cordialement
Claude
14/10/2013, 09h42
a.faurie

nikel !
merci bcp !
j'ai modifié 2 ou 3 trucs mais ça marche bien.
juste une dernière question.
je n'arrive pas la faire partir automatiquement sur une modification de cellule, je sais pas pourquoi mais ça bug alors que un lancement par BP fonction...