recopie formule SOMMEPROD en VBA [Résolu]

[ericdev67]

Bonjour,

J'utilise la fonction SOMMEPROD pour retrouver des valeurs dans un tableau.
Colonne A : noms
Colonne B : numéro semaine
Colonne C : activité
Colonne D : résultat

en H2, j'ai mis la formule

Code :

=SOMMEPROD(($A$2:$A$1791=$F2)*($B$2:$B$1791=H$1)*($C$2:$C$1791=$G2)*($D$2:$D$1791))

Grâce à l'assistant, j'ai transcris ma formule en VBA

Code :

1
2
3
4
Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((R2C[-7]:R1791C[-7]=RC[-2])*(R2C[-6]:R1791C[-6]=R1C)*(R2C[-5]:R1791C[-5]=RC[-1])*(R2C[-4]:R1791C[-4]))"
    Range("H3").Select

Mon souci est que suivant les semaines, j'ai des données qui changent (=1791 peut devenir 1980 par exemple), et étant donné que SOMMEPROD ne fonctionne pas avec des cellules vides, je suis "obligé" de chercher la dernière ligne à chaque fois pour adapter ma formule.

J'ai donc fait :

Code :

1
2
3
4
5
dernierevaleur = Sheets("Totaux").Range("a2").End(xlDown).Row
 
    Range(Cells(2, position), Cells(2, position)).Select
   ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((R2C[-7]:R & dernierevaleur & C[-7]=RC[-2])*(R2C[-6]:R & dernierevaleur & C[-6]=R1C)*(R2C[-5]:R & dernierevaleur & C[-5]=RC[-1])*(R2C[-4]:R & dernierevaleur & C[-4]))"

Mais ça ne fonctionne pas....: lorsque ma formule est recopiée, j'ai :

Code :

=SOMMEPROD((V$2:2:2 & dernierevaleur & V:V=AA2)*(W$2:2:2 & dernierevaleur & W:W=AC$1)*(X$2:2:2 & dernierevaleur & X:X=AB2)*(Y$2:2:2 & dernierevaleur & Y:Y))

= il prend "dernierevaleur" comme un texte.....

Une solution, une idée ?

Merci

A+

[jfontaine]

Bonjour,

il te manque les double quote pour sortir et entrée dans la zone de texte

Code :

1
2
3
Range(Cells(2, position), Cells(2, position)).Select
   ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((R2C[-7]:R" & dernierevaleur & "C[-7]=RC[-2])*(R2C[-6]:R" & dernierevaleur & "C[-6]=R1C)*(R2C[-5]:R" & dernierevaleur & "C[-5]=RC[-1])*(R2C[-4]:R" & dernierevaleur & "C[-4]))"

et étant donné que SOMMEPROD ne fonctionne pas avec des cellules vides, je suis "obligé" de chercher la dernière ligne à chaque fois pour adapter ma formule.

Je suis étonné de cela. j'utilise très souvent le sommeprod et je borne bien plus loin que la dernière ligne (prévision de 12 mois de données)

[ericdev67]

Merci jfontaine,


Lorsque je fais :

Code :

=SOMMEPROD(($A$2:$A$10000=$F2)*($B$2:$B$10000=H$1)*($C$2:$C$10000=$G2)*($D$2:$D$10000))

j'ai comme résultat #VALEUR et il me semblait avoir compris que ça venait des cellules vides.....

Mais peut-être tu as une autre solution et/ou une autre explication (l'utilisation de la formule jusqu'à 10000 me faciliterai la tache (=évite d'utiliser le code pour "dernierevaleur" ) ?

Merci,

A+

[jfontaine]

Je te confirme, la formule ci dessous me retourne bien le bon nombre d'enregistrement alors que mes données s'arrêtent ligne 10

Code :

=SOMMEPROD(($A$1:$A$14="lundi")*($B$1:$B$14="mardi")*($C$1:$C$14=G1))

Cela dit il n'est pas inutile d'appliquer la méthode de la dernière valeur car allonger la plage de données alourdi les calculs

[ericdev67]

Merci jfontaine,

Je ne comprends pas pourquoi ta formule fonctionne et pas la mienne....certainement liée aux données....à voir.....

Mais finalement, je te rejoins : en mettant les formules sur autant de données "alourdi" la séquence calcul, je vais donc utiliser ta première réponse ( et faire tourner le code.

Merci pour ton aide

A+ pour de prochaines aventures......

[mercatog]

Code :

*($D$2:$D$10000)

Ici tu fais la somme des données de la plage D2: D10000 répondant aux critères.
Toutes les valeurs de la plage D2 10000 doivent être numériques.

Je confirme la remarque de jfonatine. Au lieu de faire la formule jusqu'à la 10000ème ligne, contente toi du nombre de lignes effectifs.

Sinon, pour aller direct, j'utiliserai comme ceci (formules en colonne H en fonction des critères en colonnes F et G et dans la cellule H1), à adapter

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Dim LastLigA As Long, LastLigF As Long
 
Application.ScreenUpdating = False
With Worksheets("Feuil1")                        'A adapter
    LastLigA = .Cells(.Rows.Count, "A").End(xlUp).Row
    LastLigF = .Cells(.Rows.Count, "F").End(xlUp).Row
    .Range("H2:H" & LastLigF).FormulaR1C1 = "=SUMPRODUCT((R2C[-7]:R" & LastLigA & "C[-7]=RC[-2])*(R2C[-6]:R" & LastLigA & "C[-6]=R1C)*(R2C[-5]:R" & LastLigA & "C[-5]=RC[-1])*(R2C[-4]:R" & LastLigA & "C[-4]))"
 
 
'Au cas où on désire remplacer les formules par leur valeur
'    With .Range("H2:H" & LastLigF)
'        .FormulaR1C1 = "=SUMPRODUCT((R2C[-7]:R" & LastLigA & "C[-7]=RC[-2])*(R2C[-6]:R" & LastLigA & "C[-6]=R1C)*(R2C[-5]:R" & LastLigA & "C[-5]=RC[-1])*(R2C[-4]:R" & LastLigA & "C[-4]))"
'        .Value = .Value
'    End With
End With