Remplacer longueur d'une plage par une variable dans une formule matricielle

[Tr4ks]

Bonjour,

J'essaye de créer un tableau dont la taille change en fonction du nombre de données en entrée, j'utilise donc le VBA.

Pour remplir ce tableau j'utilise la formule suivante :

Cells(147 + i, 2 + j).FormulaArray = _
"=SUM(IF((Energie!R2C3:R6000C3=R146C)*(Energie!R2C4:R6000C4=RC1),Energie!R2C8:R6000C8,0))"

Le calcul étant très long je voudrai ramener la longueur des plages utilisées de 6000 à N (longueur réelle) que j'ai calculé avant, mais je ne trouve pas comment faire.

Si quelqu'un peut m'aider.

 

[ERIC S]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Bonjour

de tête (non testé en réel)
supposons que ta colonne A est la plus longue
pour déterminer la dernière ligne :

derligne=range("A" & rows.count).end(xlup).row

et pour ta formule,

"=SUM(IF((Energie!R2C3:R6000C3=R146C)*(..... va devenir

"=SUM(IF((Energie!R2C3:R" & derligne & "C3=R146C)*(....

 

[Tr4ks]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Merci, ça marche !
J'avais essayé quelque chose du style mais il devait me manquer un symbole, par contre ça n'a pas arrangé le temps de calcul
Mais merci quand même.

 

[ERIC S]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Re

normalement tu peux remplir toute une zone à la fois

Range("A3:B500).FormulaArray = _
"=SUM(IF((Energie!R2C3:R6000C3=R146C)*(Energie!R2C 4:R6000C4=RC1),Energie!R2C8:R6000C8,0))"

tu peux aussi utiliser

aplication.scrennupdating=false
...toncode
application.screenupdating=true

ou aussi

Application.Calculation = xlCalculationManual
ton code
Application.Calculation = xlCalculationAutomatic

 

[Tr4ks]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

J'ai essayé la première méthode mais lorsque je remplis tout d'un coup, il n'effectue pas de décalage sur "R146C" et "RC1", et je me retrouve avec la même valeur dans toutes les cases.

 

[ERIC S]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

RE

il faudrait que tu précises un peu

ton tableau entre quelle cellule et quelle cellule
la formule que tu obtiens dans la première cellule

il faut juste mettre des variables dans le vba, tes précisions me permettront de les déterminer

 

[Tr4ks]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Le tableau démarre en
B147 =SOMME(SI((Energie!$C$2:$C$338=B$146)*(Energie!$D$2:$D$338=$A147);Energie!$H$2:$H$338;0))

il faudrait en
C147 =SOMME(SI((Energie!$C$2:$C$338=C$146)*(Energie!$D$2:$D$338=$A147);Energie!$H$2:$H$338;0))
en
B148 =SOMME(SI((Energie!$C$2:$C$338=B$146)*(Energie!$D$2:$D$338=$A148);Energie!$H$2:$H$338;0))

En fait la colonne A du tableau que je veux remplir contient les années de référence pour le calcul, et la ligne 146 les noms.

Je voudrai remplir mon tableau avec la somme des tout ce qui correspond à l'année et au nom dans la feuille "Energie" contenant les données, dont la colonne C contient les noms, la D les années, et la H les valeurs à additionner.

Si je ne suis pas très clair, je prendrai le temps de faire un fichier pour expliquer demain.

Merci pour ton aide.

 

[ERIC S]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Re
e
ffectivement si tu peux faire un fichier, ce sera plus simple
A demain

 

[Tr4ks]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Salut Eric,

J'ai créé un fichier simplifié reprenant tous les mécanismes de calculs et la macro, j'espère que ça sera assez clair, j'ai essayé de faire au mieux.

L'onglet général est "Calcul", qui utilise "Energie" pour les données.

Le fichier original étant trop lourd.

La feuille "Energie" contenant un nombre de données et d'années variables dans le fichier original (import de données avant calcul).

 

[ERIC S]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Bonjour

à voir si c'est plus rapide

derligne = Sheets("Energie").Range("A" & Rows.Count).End(xlUp).Row
Application.Calculation = xlCalculationManual
    Range("B2").FormulaArray = _
    "=SUM(IF((Energie!R2C1:R" & derligne & "C1=""eclairage"")*(Energie!R2C3:R" & derligne & "C3=R1C)*(Energie!R2C2:R" & derligne & "C" & i & "=RC1),Energie!R2C4:R" & derligne & "C4,0))"
    Range("B2").AutoFill Destination:=Range("B2:C2"), Type:=xlFillDefault
    Range("B2:C2").AutoFill Destination:=Range("B2:C5"), Type:=xlFillDefault
Application.Calculation = xlCalculationAutomatic

les B2, C2 et autres sont à remplacer par une zone variable bien sûr

 

[ERIC S]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Re

la manip parait plus simple avec sommeprod

derligne = Sheets("Energie").Range("A" & Rows.Count).End(xlUp).Row
 Application.Calculation = xlCalculationManual
     Range("B2:C5").Formula = _
        "=SUMPRODUCT((Energie!R2C1:R" & derligne & "C1=""eclairage"")*(Energie!R2C3:R" & derligne & "C3=R1C)*(Energie!R2C2:R" & derligne & "C2=RC1),Energie!R2C4:R" & derligne & "C4)"
Application.Calculation = xlCalculationAutomatic

 

[Tr4ks]

Re : Remplacer longueur d'une plage par une variable dans une formule matricielle

Les trois méthodes mettent exactement le même temps pour réaliser le calcul.
Mais merci quand même pour ton aide.