Calculer une même formule sur plage de cellule qui changera en VBA

roleca7 déclare :
For Sheets (i) =3 To Sheets (4)
Next i


Je pense que tu as voulu écrire
For I=3 to 4
feuille=Sheets(I)
next I

Ensuite si j'ai bien compris ce que tu veux faire ton code va modifier les
cellules au fur et à mesure : est-ce bien ce que tu veux ?

Exemple

cellule 1,1 vaut 1
cellule 1,2 vaut 2
cellule 1,3 vaut 3
donc la colonne vaut 6
après l'exécution de ton code tu auras

cellule 1,1 vaudra 100/6 soit 16.7 environ, mais la somme de la colonne vaut
maintenant 21.7
donc cellule 1,2 vaudra 200/21.7 soit 9.2 et la somme de la colonne vaut
28.9
par conséquent la cellule 1,3 contiendra 300/28.9 soit 10.4 environ, la
somme de la colonne vaut alors 36.3.

Précise.

Enfin tu peux faire une boucle while qui teste tant qu'une cellule est non
vide,
Tu peux aussi créer dans ta sub une inputbox qui te demandera quelles ligne
et quelles colonnes utiliser. Tu peux aussi créer une fonction qui prendra
en argument une plage de cellules.

Maintenant tu as plus simplement la fonction moyenne toute faite puisque
qu'en réalité c'est ce que tu calcules (multipliée par 100). Cette fonction
a l'avantage d'ignorer les cellules ne comportant pas des nombres (de
mémoire et à vérifier).



"roleca7" <vicsteph@hotmail.com> a écrit dans le message de news:
1144304405.702514.291240@i39g2000cwa.googlegroups.com...
Bonjour à tous, je souhaite appliquer une même formule sur une plage
de cellules qui pourra varier dans le temps sur 2 onglets particuliers
en VBA.

J'ai commencé par faire la boucle suivante étant donné que je
connais déjà les 2 onglets qui m'intéressent : les onglets 3 et 4
de mon classeur.

For Sheets (i) =3 To Sheets (4)
Next i

Mais je ne sais pas comment faire pour appliquer la formule car pour
l'instant ma plage de cellule va de la cellule C6 à F13 mais à
l'avenir, le nombre de lignes vers le bas pourra changer ainsi que le
nombre de colonnes vers la droite.

La formule qui sera à effectuer pour toutes les cellules où il y a
des nombres (il faut donc gérer le cas où il n'y a pas de chiffres)
sera la suivante : (le chiffre de la cellule *100)/le total de la
colonne faisant référence à cette cellule.

Merci d'avance si vous trouver le code vba exact ou si vous pouvez
m'avancer dans ma recherche.

Dans le message 1144304405.702514.291240@i39g2000cwa.googlegroups.com,
roleca7 <vicsteph@hotmail.com> a écrit :
(...)

bonjour ,

La formule qui sera à effectuer pour toutes les cellules où il y a
des nombres (il faut donc gérer le cas où il n'y a pas de chiffres)
sera la suivante : (le chiffre de la cellule *100)/le total de la
colonne faisant référence à cette cellule.

ce n'est pas très clair
...
dès le titre :

"Calculer une même formule sur plage de cellule qui changera en VBA"

S'agit-t-il d'appliquer une même formule
avec une macro (en VBA)
sur une plage variable...

ou
est-ce VBA qui provoquera le changement de plage ?

.... mais si j'ai bien compris :
avec des formules, tu tomberas sur une référence circulaire
puisque la future valeur de la cellule D3 ( par ex. )
dépendra doublement de l'ancienne valeur de D3
( valeur de D3 et somme de la colonne D ...)
dès la première modif, les autres calculs donneront des résultats
"imprévus"
A moins de mettre "itération à 1"
mais ceux qui savent ce servir
de ce type de raffinement sont rares...
L'idée même d'utiliser des formules pour faire cela me semble
absurde...
ou alors
les valeurs résultantes sont aux mêmes adresses sur une autre
feuille...
auquel cas c'est élémentaire :
il suffit de remplir une zone assez grande avec des formules pour
couvrir la plage maximale possible
avec un test ... pour savoir si on met qqchose...

Ainsi feuil2!D3 contient :

=Si(feuil1!D3="";"";100*feuil1!D3/somme(feuil1!D:D))

Si les sommes des colonnes peuvent être nulles sans que les cellules
soient toutes vides, il faut améliorer :

=Si(feuil1!D3="";"";si(somme(feuil1!D:D)=0;"";100*feuil1!D3/somme(feuil1!D:D)))

je ne vois donc pas à quoi une macro peut bien servir...


En revanche, tu peux utiliser une macro pour remplacer les valeurs.
( les cellules contiendront, à la fin, des nombres... )
Mais est-il bien raisonnable de l'envisager ...

HB

Merci pour votre aide qui m'aide à mieux comprendre le pb !
Mais je ne pense pas que la moyenne fera ce que je veux...

Admettons que
cell1 = 2
cell 2 = 3
cell 3 = 4
cell 4 = 1 et donc que mon total de colonne c5=10 ; cela équivaudrait
à une sorte de pourcentage puisque au résultat cell 1 = 20, cell 2 =
30, cell 3 = 40 et cell 5 = 100

J'ai donc défini une plage où il y a les données à calculer (y
compris les totaux) et une plage où il n' y a que les totaux des
colonnes et j'ai écris la ligne de code suivante :

Dim i As Integer

For i = 3 To 4
Sheets(i).Range("maplage").Cells.Formula = Range("maplage").Cells * 100
/ Range("montotal").Cells

Next i

Ce code ne fonctionne pas car cela me met un erreur d'exécution 13 :
incompatibilité de type...

Je ne sais pas comment désigner les cellules actives pour lui dire je
veux faire la multiplication par 100 de toutes mes cellules actives
dans ma plage, divisées par les cellules de chaque total des
colonnes...

Avez-vous une idée ou même le code exact ?

Merci beaucoup Bayosky, ta methode sans passer par une macro me
convient parfaitement mais j'ai encore 2 questions :

Ta formule fonctionne sauf que je me trouve avec le résultat divisé
par 2 à chaque fois et je ne vois pas où çà cloche (essaies de
tester ta formule)


Avec cette formule, si j'ajoute des lignes ou des colonnes, cela ne
marche plus et je suis obligé de réappliquer la formule sur ma
nouvelle plage, par une macro n'est-il pas possible d'inclure cette
possibilité et d'aller mettre les résultats sur une autre feuille
comme vous me l'avez suggéré ? Si oui, comment ?

Merci d'avance et encore merci pour votre aide.

Bonjour,

D'une part, bien lire l'intervention de Bayosky.

D'autre part, il y a quelque chose qui cloche sur cette ligne :

Sheets(i).Range("maplage").Cells.Formula = Range("maplage").Cells * 100
/ Range("montotal").Cells

En effet, à gauche on a une "Formula", donc si je ne m'abuse une donnée
de type String, alors qu'à droite on a une "Value", qui si je ne m'abuse
est de type numérique.

Essayer de mettre des nombres dans une formule, ça risque de coincer.
A moins qu'on mette un "=" devant la conversion en chaîne de caractères,
mais même si tous les chemins mènent à Rome, faire le tour du monde pour
aller de République à Bastille ne paraît guère indispensable.

Il y a une démarche que je serais tenté de proposer sur cette question :
fermer le fichier, passer un bon week-end, et y réfléchir à nouveau
Lundi matin en remettant tout à plat.

En fait, une macro pourrait être bien si on voulait développer un
"assistant" pour générer les proportions par rapport aux cellules
sélectionnées. Si j'ai bien suivi on verra ça plus tard, ça vaudra mieux.

Il est assez classique de présenter une valeur absolue dans une colonne,
et dans la colonne à droite faire apparaître la proportion par rapport
au total. C'est le plus souvent sur cet exemple qu'on base les exercices
pour mettre en œuvre la distinction entre formule relative et formule
absolue.

Sur les versions que j'ai utilisées on utilise la touche F4 dans la zone
d'édition de formule pour faire la bascule entre les deux, ce qui fait
apparaître ou disparaître un signe $ dans la formule. Lorsque la formule
contient $ pour désigner une cellule, cette cellule reste valable
lorsqu'on copie la formule en-dessous. Si il n'y a pas de $ au niveau du
numéro de ligne, en copiant la formule en-dessous on se retrouve avec un
de plus au niveau du numéro de ligne. En prenant bien soin d'avoir les $
là où il faut et pas ailleurs, une fois la première formule de
proportion réalisée, il suffit de sélectionner la colonne des
proportions et de presser Ctrl B (copie vers le bas).

Après il ne reste plus que la mise en forme à réaliser. On a droit à
deux minutes trente par tableau pour le tout en utilisant la mise en
forme automatique (cinq minutes la première fois, quand même pas exagérer).


Exemple
C1 = 1
C2 = 3
C3 = 2
C4 = 4
C5 = Sum(C1:C4)

On mettra en D1 :
= C1 * 100 / $C$5

Sélectionner D1:D5, presser Ctrl B, dans D5 on devrait voir 100.
La formule de D2 sera :
= C2 * 100 / $C$5

On voit que C1 est devenu C2 puisqu'à ce niveau la formule est relative,
alors que C5 est resté C5 puisqu'à ce niveau la formule est absolue, ce
qui est exprimé par les signes $ dans la formule.

Mais au fait, ce que je dis là aurait sa place plutôt dans
microsoft.public.fr.excel, non ?

J'espère que je ne me suis pas mélangé les pinceaux, je n'ai rien sous
la main pour tester, aujourd'hui.

Jeter un coup d'œil aux tableaux de résultats publiés par une banque ou
une entreprise, pour voir des exemples de mise en forme.

Merci Gloops, ce que tu m'as proposé fonctionne très bien mais il y
a juste 2 choses qui me tracassent :

En fait ma plage où sont mes données, sans les totaux, est C6 : F14,
mes totaux se trouvant sur la ligne 15 (donc de C15 à F15).

Donc j'aimerais avoir mes résultats de formule dans une autre
feuille du classeur (au lieu de les mettre dans la colonne D d'à
côté comme tu le préconises) ce qui fait que mes résultats seront
dans la plage C6 : F15 de l'autre feuille.

Et donc ma copie de formule n'est plus bonne car elle m'oblige à
changer, à chaque changement de colonne, la cellule de la somme car
'mafeuille'!$C$15 reste sur toutes les cellules de la formule copiée,
alors que je devrais avoir 'mafeuille'!$D$15 pour la colonne D,
'mafeuille'!$E$15 pour la colonne E...

As-tu une meilleure solution que de changer à chaque fois ma cellule
de valeur absolue ?

Si oui merci de me tenir au courant.

Encore merci !


Gloops a écrit :

En fait, une macro pourrait être bien si on voulait développer un
"assistant" pour générer les proportions par rapport aux cellules
sélectionnées. Si j'ai bien suivi on verra ça plus tard, ça vaudr a mieux.

Il est assez classique de présenter une valeur absolue dans une colonne,
et dans la colonne à droite faire apparaître la proportion par rapport
au total. C'est le plus souvent sur cet exemple qu'on base les exercices
pour mettre en œuvre la distinction entre formule relative et formule
absolue.

Sur les versions que j'ai utilisées on utilise la touche F4 dans la zone
d'édition de formule pour faire la bascule entre les deux, ce qui fait
apparaître ou disparaître un signe $ dans la formule. Lorsque la form ule
contient $ pour désigner une cellule, cette cellule reste valable
lorsqu'on copie la formule en-dessous. Si il n'y a pas de $ au niveau du
numéro de ligne, en copiant la formule en-dessous on se retrouve avec un
de plus au niveau du numéro de ligne. En prenant bien soin d'avoir les $
là où il faut et pas ailleurs, une fois la première formule de
proportion réalisée, il suffit de sélectionner la colonne des
proportions et de presser Ctrl B (copie vers le bas).

Après il ne reste plus que la mise en forme à réaliser. On a droit à
deux minutes trente par tableau pour le tout en utilisant la mise en
forme automatique (cinq minutes la première fois, quand même pas exag érer).


Exemple
C1 = 1
C2 = 3
C3 = 2
C4 = 4
C5 = Sum(C1:C4)

On mettra en D1 :
= C1 * 100 / $C$5

Sélectionner D1:D5, presser Ctrl B, dans D5 on devrait voir 100.
La formule de D2 sera :
= C2 * 100 / $C$5

On voit que C1 est devenu C2 puisqu'à ce niveau la formule est relative,
alors que C5 est resté C5 puisqu'à ce niveau la formule est absolue, ce
qui est exprimé par les signes $ dans la formule.

Mais au fait, ce que je dis là aurait sa place plutôt dans
microsoft.public.fr.excel, non ?

J'espère que je ne me suis pas mélangé les pinceaux, je n'ai rien s ous
la main pour tester, aujourd'hui.

Jeter un coup d'œil aux tableaux de résultats publiés par une banqu e ou
une entreprise, pour voir des exemples de mise en forme.

Bonjour,

Se pourrait-il que tu n'aies pas compris la notion d'adresse relative
par opposition à adresse absolue ?

Si tu veux que ton adresse s'ajuste aussi bien en ligne qu'en colonne,
il faut qu'elle soit relative dans les deux sens.

Donc, en 'FeuilleTotal'!C15, 'Feuille1'!C$15
Sélection 'FeuilleTotal'!C15:F15, copier à droite.

En 'FeuilleTotal'!C6, 'Feuille1'!C6 * 100 / $C$15
Sélection 'FeuilleTotal'!C6:C14, copier en bas.
Sélection 'FeuilleTotal'!C6:F14, copier à droite

Mise à jour des calculs (touche F9 si je ne m'abuse).

Raccourcis clavier :
copier à droite : Ctrl D
copier en bas : Ctrl B

Je te laisse ajuster les noms de feuilles, je crois qu'à la place de
Feuille1 c'est maFeuille.

Prochains exercices :

1/ justifier chaque étape dans la procédure ci-dessus, préciser pourquoi
chaque formule comporte un dollar devant la lettre de la colonne ou pas,
pourquoi elle comporte un dollar devant le numéro de ligne ou pas.

2/ chaque mois occupe deux colonnes, une pour les valeurs absolues, une
pour la proportion du chiffre de l'année. Une paire de colonnes à droite
pour le total de l'année. Même chose pour plusieurs secteurs, à raison
d'une feuille par secteur. Une feuille récapitulative à la fin, qui
présente la proportion de chaque mois dans le chiffre de l'année, tous
secteurs confondus.

Une fois que c'est fait tu racontes comment tu t'y es pris.
_________________
roleca7 a écrit :

Merci Gloops, ce que tu m'as proposé fonctionne très bien mais il y
a juste 2 choses qui me tracassent :

En fait ma plage où sont mes données, sans les totaux, est C6 : F14,
mes totaux se trouvant sur la ligne 15 (donc de C15 à F15).

Donc j'aimerais avoir mes résultats de formule dans une autre
feuille du classeur (au lieu de les mettre dans la colonne D d'à
côté comme tu le préconises) ce qui fait que mes résultats seront
dans la plage C6 : F15 de l'autre feuille.

Et donc ma copie de formule n'est plus bonne car elle m'oblige à
changer, à chaque changement de colonne, la cellule de la somme car
'mafeuille'!$C$15 reste sur toutes les cellules de la formule copiée,
alors que je devrais avoir 'mafeuille'!$D$15 pour la colonne D,
'mafeuille'!$E$15 pour la colonne E...

As-tu une meilleure solution que de changer à chaque fois ma cellule
de valeur absolue ?

Si oui merci de me tenir au courant.

Encore merci !