VBA - Adresse cellule ds variable

[VBA - Adresse cellule ds variable]

Bonjour,

comme vous avez du l'entendre maintes et maintes fois sur le forum, 'je débute en VBA'... et je tiens à m'excuser au préalable par le caractère simpliste de ma question. Néanmoins, je bloque depuis plus d'une heure et je me dis que qulqu'un pourrait m'aider.

Mon besoin :
Je veux calculer la moyenne d'une plage qui démarre à la cellule E3 (c'est toujours le cas) et dont la longuer varie en fonction du nombre d'enregistrements (je retraite des questionnaires).
Le nombre d'enregistrement est stocké dans la variable nbEnreg

Mon code :
a = 'E3'
b = Offset(0,nbEnreg).Address
ActiveCell.FormulaR1C1 = '=AVERAGE(' & a & ':' & b & ')'

Mais ça ne marche pas... visiblement ce n'est pas la bonne syntaxe ;-)
Si vous avez des suggestions, je suis plus que preneur !!


Merci 🙂

 

[CBernardT]

Bonjour Inconnu(e),

Une solution :

Tu définis la plage de travail par le menu Insertion/Nom/Définir ;
1- Dans 'Noms dans le classeur', écrire 'Plage' (pour l'exemple) ;
2- Dans 'Fait référence à :' tu écris la formule :

=DECALER(Feuil1!$E$3;0;0;NBVAL(Feuil1!$E:$E))
3- OK

Dans la cellule de la feuille où tu veux la moyenne, tu écris la formule :

=MOYENNE(Plage)

Sans erreur, tu as la moyenne des valeurs contenues dans la plage.

Cordialement

Bernard

 

[Marco]

Bonjour Bernard,

et un grand merci pour ta réponse !!

J'avais pensé à cette solution, mais la macro que je suis en train d'écrire doit s'appliquer à 96 classeurs différents... donc j'aimerais bien y arriver en faisant passer l'intégralité de la commande en VBA...

J'ai l'impression que j'y suis presque, sauf que quand je déclare une cellule en tant que variable, Excel la stocke avec des ' ', ce qui me donne une formule de ce type

=AVERAGE('D3':E5)

et donc ça ne marche pas...

Cdt,
Marco

 

[CBernardT]

Re Marco,

montres nous un exemple avec la macro même inachevée pour mieux situer le problème.

Cordialement

Bernard

 

[Marco]

Je joins tout le fichier... ce sera plus simple ;-)

Et d'avance merci pour ta disponibilité et ton aide...

Là j'ai déclaré dans les variables des valeurs fixes, qu'il faudra que je remplace par un objet de type Offset je pense...

Et voici le fichier :

 

[Marco]

Le fichier n'a pas été chargé... peut-être car il n'était pas zippé... je retente ;-)

[file name=FinanceMacro.zip size=11309]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/FinanceMacro.zip[/file]

 

[CBernardT]

Re Marco,

J'ai apporté quelques modifications à la macro, en espérant que cela répond à tes souhaits.

Cordialement

Bernard [file name=FinanceV1.zip size=9798]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/FinanceV1.zip[/file]

Message édité par: CBernardT, à: 02/06/2006 15:11

 

[Marco]

Meerci !!!

Ca à l'air de répondre parfaitement à ce que je voulais faire...

Il y a juste une petite chose, mais je pense que je pourrais l'adapter tout seul : la moyenne ne doit être calculé que sur les enregistrements 'chefs de division' (et pas 'Directeur central')... mais je devrais y arriver !

J'en profite pour te poser une question (désolé d'abuser de ta sympathie) :

Pourquoi avoir mis des . (points) devant cells et range en début de chaque ligne ?


Merci Bernard !

 

[CBernardT]

Re Marco,

L'instruction With permet d'appliquer une série d'instructions à un objet.
Pour modifier plusieurs propriétés d'un seul objet, il suffit de placer les instructions d'affectation de propriétés dans la structure de contrôle With. On ne fait ainsi référence qu'une seule fois à l'objet, au lieu de le faire à chaque affectation de propriété. Le point placé devant les propriétés indique cette structure.

Exemple :

With Sheets('finance')
Lig = .Range('A1000').End(xlUp).Row
Col = .Range('IV1').End(xlToLeft).Column
End With
Pour rectifier la macro et ne faire la moyenne que sur les Chefs de division tu peux modifier la ligne :

Cells(Lig + 2, i) = Application.Average(Cells(2, i), Cells(Lig, i))

par :

Cells(Lig + 2, i) = Application.Average(Cells(3, i), Cells(Lig, i))

Cordialement

Bernard

 

[Marco]

Merci !!!
C'est plus que parfait 🙂

Est-ce que c'est possible (c'est plus pour ma culture perso...) d'écrire ça sous une autre forme, dans le cas où je veux garder les formules dans la cellule de la moyenne (au lieu du résultat directement stocké sous forme de valeur) ?

Ton instruction (qui fonctionne très bien!!)
Cells(Lig + 2, i) = Application.Average(Cells(3, i), Cells(Lig, i))

Ma tentative (qui ne fonctionne pas...)
Cells(Lig + 2, i).Value = '=AVERAGE(' & Cells(3, i) & ':' & Cells(Lig, i) & ')'

Et dernière question... est-ce possible de spécifier dans les propriétés de la cellule 'moyenne' que le résultat s'affiche sur 3 décimales au lieu de 2 ?


Merci encore Bernard et bonne fin de journée

Marco

 

[Marco]

Je viens de revérifier sur tout le tableau et je crois qu'il y a une petite erreur... en mettant

Commande
Cells(Lig + 2, i) = Application.Average(Cells(3, i), Cells(Lig, i))

il fait bien la moyenne, mais de Cells(3, i) et de Cells(Lig, i) ... sans intégrer dans la moyenne les valeurs intérmédiaires (tous les cells(de 4 à Lig-1, i)


... heu est-ce normal?

 

[Marco]

Ahh 🙂
La satisfaction de pouvoir répondre tout seul !

Ca marche avec cette commande :
Cells(Lig + 2, i) = '=Average(' & Cells(3, i).Address & ':' & Cells(Lig, i).Address & ')'

Pour la question de l'arrondi, je chercherai plus tard... je vais avancer sur le fond de la macro car il me reste encore pas mal de travail 🙂)

Merci encore Bernard pour ton aide et ta dispo !

Très très bon week-end

Marco