Evaluate et SumProduct et colonne de plage variable

Le 20/04/2015 à 12:34

Bonjour a tous, j'ai actuellement un petit problème avec mon code. Je pense a une erreur de syntaxe sur les

Worksheets("Calcul_Intermediare").Cells(8, a) = Evaluate("=SumProduct((Zone!Cells(i,3):Cells(i,17522) * (Facteurs!Cells(w,3):Cells(w,17522))/2")

et plus particulièrement sur

Evaluate("=SumProduct((Zone!Cells(i,3):Cells(i,17522)

Comme vous pouvez le voir Cells ne reconnait pas la forme i,3 car il n'ajoute pas d'espace automatiquement après la "," . Ma question est lorsque l'on utilise Evaluate et SumProduct il y a t-il une forme de syntaxe particulière pour écrire une plage de cellule variable?

Si oui, pouvez vous m'indiquer celle-ci.

Je précise que mon code marche correctement si je remplace

Cells(i,3):Cells(i,17522)

par

(A3):(A17522)

voici le code :

Option Explicit

'**********************************Calcul intermédiaire**************************
'NORME 15978
Sub calcul_intermédiaire_norme()

Application.ScreenUpdating = False
Application.StatusBar = "Impact pour la Norme 15978 en cours..."
'variable 12 analayse (12*18 indicateurs)
Dim a As Integer

' variable conso CDC Zone
Dim i As Integer ' Chauffage
Dim j As Integer ' ECS
Dim k As Integer ' éclairage
Dim l As Integer ' elec spé

'Variable Facteurs
Dim w As Integer ' facteurs Chauffage
Dim x As Integer ' facteurs ECS
Dim y As Integer ' facteurs éclairage
Dim z As Integer ' facteurs elec spé

'incrémentation colonne zone
i = 12
j = i + 1
k = i + 2
l = i + 2

'incrémentation colonne facteur
w = 4
x = w + 1
y = w + 2
z = w + 3

    For a = 3 To 20

'chauffage
Worksheets("Calcul_Intermediare").Cells(8, a) = Evaluate("=SumProduct((Zone!Cells(i, 3):Cells(i, 17522) * (Facteurs!Cells(w,3):Cells(w,17522))/2")
'ECS
Worksheets("Calcul_Intermediare").Cells(9, a) = Evaluate("=SumProduct((Zone!Cells(j, 3):Cells(j, 17522) * (Facteurs!Cells(x,3):Cells(x,17522))/2")
'eclairage
Worksheets("Calcul_Intermediare").Cells(10, a) = Evaluate("=SumProduct((Zone!Cells(k, 3):Cells(k, 17522) * (Facteurs!Cells(y,3):Cells(y,17522))/2")
' Elec spé
Worksheets("Calcul_Intermediare").Cells(11, a) = Evaluate("=SumProduct((Zone!Cells(l, 3):Cells(l, l17522) * (Facteurs!Cells(z,3):Cells(z,17522))/2")

i = i + 23
w = w + 4
Next a

End Sub

Merci pour l'aide que vous pourrez m'apporter.

Cordialement.

Le 20/04/2015 à 13:16

Bonjour

Moi qui tatonne en VBA, sans fichier pour contrôler, je suis bien démuni

au pif, essaie en modifiant

Evaluate("=SumProduct((Zone!Cells(" & i & ",3):Cells(" & i & ",17522)

Cordialement

Le 20/04/2015 à 13:19

Bonjour,

17522 colonnes, ça me parait beaucoup !

Essaie avec :

Worksheets("Calcul_Intermediaire").Cells(8, a) = Evaluate("SumProduct((Zone!" & Cells(i, 3).Resize(1, 1500).Address & ") * (Facteurs!" & Cells(w, 3).Resize(1, 1500).Address & "))/2")

A+

Le 20/04/2015 à 13:59
frangy a écrit :

Bonjour,

17522 colonnes, ça me parait beaucoup !

Essaie avec :

Worksheets("Calcul_Intermediaire").Cells(8, a) = Evaluate("SumProduct((Zone!" & Cells(i, 3).Resize(1, 1500).Address & ") * (Facteurs!" & Cells(w, 3).Resize(1, 1500).Address & "))/2")

A+

Effectivement se sont des lignes. je ne comprend pas le .Resize

de plus le problème n'est pas résolu par les 2 méthodes

Le 20/04/2015 à 14:24

Le Resize permet de désigner une plage de cellule.

Exemple : la plage C3:Q3 peut être définie comme étant la cellule C3 étendue sur 1 ligne et 15 colonnes, soit Cells(3, 3).Resize(1, 15).

La ligne d'instruction que je t'ai envoyée est correcte (en règle générale, je fais le test avant de poster).

Reste à voir ce que tu entends par "problème non résolu".

Remarque : dans la formule, j'ai corrigé le nom de la feuille "Calcul_Intermediaire" qui était écrit "Calcul_Intermediare"

A+

Le 20/04/2015 à 14:36
frangy a écrit :

Le Resize permet de désigner une plage de cellule.

Exemple : la plage C3:Q3 peut être définie comme étant la cellule C3 étendue sur 1 ligne et 15 colonnes, soit Cells(3, 3).Resize(1, 15).

La ligne d'instruction que je t'ai envoyée est correcte (en règle générale, je fais le test avant de poster).

Reste à voir ce que tu entends par "problème non résolu".

Remarque : dans la formule, j'ai corrigé le nom de la feuille "Calcul_Intermediaire" qui était écrit "Calcul_Intermediare"

A+

Du coup je ne comprend pas

Worksheets("Calcul_Intermediaire").Cells(8, a) = Evaluate("SumProduct((Zone!" & Cells(i, 3).Resize(1, 1500).Address & ") * (Facteurs!" & Cells(w, 3).Resize(1, 1500).Address & "))/2")
.Resize(1, 1500).Address & "

la raison des 1500 ?

désolé (je ne suis pas vraiment un expert en VBA), si mes questions peuvent paraitre idiotes.

Mon code semi-corrigé donne :

'Chauffage
Worksheets("Calcul_Intermediaire").Cells(8, a) = Evaluate("=SumProduct((Zone!Cells(3,i):Cells(17522,i) * (Facteurs!(Cells(3,w):Cells(17522,w)/2")
'ECS
Worksheets("Calcul_Intermediaire").Cells(9, a) = Evaluate("=SumProduct((Zone!Cells(3,j):Cells(17522,j) * (Facteurs!Cells(3,x):Cells(17522,x))/2")
'eclairage
Worksheets("Calcul_Intermediaire").Cells(10, a) = Evaluate("=SumProduct((Zone!Cells(3,k):Cells(17522,k) * (Facteurs!Cells(3,y):Cells(17522,y))/2")
' Elec spé
Worksheets("Calcul_Intermediaire").Cells(11, a) = Evaluate("=SumProduct((Zone!Cells(3,l):Cells(17522,l) * (Facteurs!Cells(3,z):Cells(17522,z))/2")

j'ai essayé de montre des " avant le premier

'Chauffage
Evaluate("=SumProduct((Zone!"Cells(3, i):Cells(17522,i) * (Facteurs!(Cells(3,w):Cells(17522,w)/2")

Ce qui réactive bien la fonction Cells, qui créer un espace automatiquement apres la , mais je ne sais pas ou je dois mettre le suivant ".

Le 20/04/2015 à 14:46

J'ai mis 1500 simplement parce que je ne pouvais pas laisser 17522.

Tu as dit t'être trompé entre les lignes et les colonnes, as-tu fait la correction ?

Si tu me dis clairement quelles sont les plages à prendre en compte, je te dirai quelle instruction appliquer.

A+

Le 20/04/2015 à 15:00
frangy a écrit :

J'ai mis 1500 simplement parce que je ne pouvais pas laisser 17522.

Tu as dit t'être trompé entre les lignes et les colonnes, as-tu fait la correction ?

Si tu me dis clairement quelles sont les plages à prendre en compte, je te dirai quelle instruction appliquer.

A+

Oui, j'ai bien fait la correction, voici le code :

Option Explicit

'**********************************Calcul intermédiaire**************************
'NORME 15978
Sub calcul_intermédiaire_norme()

Application.ScreenUpdating = False
Application.StatusBar = "Impact pour la Norme 15978 en cours..."
'variable 12 analayse (12*18 indicateurs)
Dim a As Integer

' variable conso CDC Zone
Dim i As Integer ' Chauffage
Dim j As Integer ' ECS
Dim k As Integer ' éclairage
Dim l As Integer ' elec spé

'Variable Facteurs
Dim w As Integer ' facteurs Chauffage
Dim x As Integer ' facteurs ECS
Dim y As Integer ' facteurs éclairage
Dim z As Integer ' facteurs elec spé

'incrémentation colonne zone
i = 12
j = i + 1
k = i + 2
l = i + 2

'incrémentation colonne facteur
w = 4
x = w + 1
y = w + 2
z = w + 3

    For a = 3 To 20

'Chauffage
Worksheets("Calcul_Intermediaire").Cells(8, a) = Evaluate("=SumProduct((Zone!Cells(3,i):Cells(17522,i) * (Facteurs!(Cells(3,w):Cells(17522,w)/2")
'ECS
Worksheets("Calcul_Intermediaire").Cells(9, a) = Evaluate("=SumProduct((Zone!Cells(3,j):Cells(17522,j) * (Facteurs!Cells(3,x):Cells(17522,x))/2")
'eclairage
Worksheets("Calcul_Intermediaire").Cells(10, a) = Evaluate("=SumProduct((Zone!Cells(3,k):Cells(17522,k) * (Facteurs!Cells(3,y):Cells(17522,y))/2")
' Elec spé
Worksheets("Calcul_Intermediaire").Cells(11, a) = Evaluate("=SumProduct((Zone!Cells(3,l):Cells(17522,l) * (Facteurs!Cells(3,z):Cells(17522,z))/2")

i = i + 23
w = w + 4
Next a

End Sub

En résumé :

Pour a = 3
'chauffage
Worksheets("Calcul_Intermediare").Cells(8, a) = Evaluate("=SumProduct((Zone!L3:L17522) * (Facteurs!D3:D17522))/2")
'ECS
Worksheets("Calcul_Intermediare").Cells(9, a) = Evaluate("=SumProduct((Zone!L3:L17522)  * (Facteurs!E3:E17522))/2")
'eclairage
Worksheets("Calcul_Intermediare").Cells(10, a) = Evaluate("=SumProduct((Zone!Zone!M3:M17522) ) * (Facteurs!F3:F17522))/2")
' Elec spé
Worksheets("Calcul_Intermediare").Cells(11, a) = Evaluate("=SumProduct((Zone!Zone!N3:N17522)  * (Facteurs!G3:G17522))/2")

une fois que cette étape est fini , je souhaiterai que je passe à un autre calcul qui donnerai :

En résumé :

Pour a = 4
'chauffage
Worksheets("Calcul_Intermediare").Cells(8, a) = Evaluate("=SumProduct((Zone!AI3:AI17522) * (Facteurs!H3:H17522))/2")
'ECS
Worksheets("Calcul_Intermediare").Cells(9, a) = Evaluate("=SumProduct((Zone!AJ3:AJ17522)  * (Facteurs!I3:I17522))/2")
'eclairage
Worksheets("Calcul_Intermediare").Cells(10, a) = Evaluate("=SumProduct((Zone!Zone!AK3:AK17522) ) * (Facteurs!J3:J17522))/2")
' Elec spé
Worksheets("Calcul_Intermediare").Cells(11, a) = Evaluate("=SumProduct((Zone!Zone!AL3:AL17522)  * (Facteurs!K3:K17522))/2")

Et pour a de 3 à 110.

D'où ma boucle avec toutes les variables. Le probleme c'est

Worksheets("Calcul_Intermediare").Cells(8, a) = Evaluate("=SumProduct((Zone!AI3:AI17522) * (Facteurs!H3:H17522))/2")

avec

Zone!AI3:AI17522)

est très bien compris sous cette forme. Cependant des que je passe sous la forme "variable pour ma boucle"

Zone!Cells(3,i):Cells(17522,i)

, mon résultat est affiché #Valeur, alors que dans la première forme, le résultat est calculé correctement.

Pour répondre a ta 2eme interrogation, mes plages font donc toujours 1 colonne.

cdt

Le 20/04/2015 à 15:27

Voici mon fichier test avec la formule

Worksheets("Calcul_Intermediaire").Cells(8, a) = Evaluate("=SumProduct((Zone!" & Cells(3, i).Resize(17520, 1).Address & "* Facteurs!" & Cells(3, w).Resize(17520, 1).Address & ")/2)")
18darthvader.xlsm (15.65 Ko)

A+

Le 20/04/2015 à 16:37
frangy a écrit :

Voici mon fichier test avec la formule

Worksheets("Calcul_Intermediaire").Cells(8, a) = Evaluate("=SumProduct((Zone!" & Cells(3, i).Resize(17520, 1).Address & "* Facteurs!" & Cells(3, w).Resize(17520, 1).Address & ")/2)")

A+

Re, j'ai donc ajouté mes 3 autres lignes, cependant, le calcul est inter mitant. Un coup le débogage se lance, un coup non. De plus, si lors du débogage je clic sur continuer, le calcul se fait correctement. As tu le même problème ? ou une réponse à ce nouveau problème ?

http://www.cjoint.com/15av/EDuqYi5gzzM_darthvader.xlsm

cordialement

Le 20/04/2015 à 16:53

Non, je ne rencontre pas le problème.

Pour permettre de visualiser la formule, j'ai renseigné la colonne B.

18darthvader.xlsm (19.09 Ko)

A+

Le 21/04/2015 à 16:43

Merci beaucoup pour ton aide.

J'ai à présent une autre question cette fois-ci une somme, je pense à une erreur dans la formulation ( encore une fois !!), pouvez vous m'indiquer laquelle svp ?

Worksheets("Calcul_Intermediaire").Cells(7, a) = Evaluate("=SUM((Calcul_Intermediaire!" & Cells(8, a) & "+ Calcul_Intermédiaire!" & Cells(9, a) & "+ Calcul_Intermédiaire!" & Cells(10, a) & "+ Calcul_Intermédiaire!" & Cells(11, a) & "))")

J'ai donc remplacer le SumProduct par un Sum, et j'ai également remplacé le * par un + .

Cependant j'ai actuellement #VALEUR! qui apparait.

cordialement

Le 23/04/2015 à 12:07

Bonjour,

Worksheets("Calcul_Intermediaire").Cells(7, a) = Evaluate("=SUM((Calcul_Intermediaire!" & Cells(8, a).Address & ":" & Cells(11, a).Address & "))")

A+

Rechercher des sujets similaires à "evaluate sumproduct colonne plage variable"