SOMMEPROD en VBA pour plage non vide

Bonjour,

J'ai fait une petite macro pour établir une grille de calcul simple, et il y a deux choses sur lesquelles je me questionne:

1. Est-il possible de coder les formules qui calculent seulement les plages non vide plutôt qu'une plage spécifique?

2. Pourquoi dans le classeur, le calcul ne commence pas à la ligne 2 pour les formules qui concernent Honneywell, SPERIAN et Starfield?

Range("P3").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""Innotex""))"
Range("Q3").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""Innotex""))"
Range("R3").FormulaR1C1 = "=RC[-2]+RC[-1]"

Range("P4").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""Honeywell""))"
Range("Q4").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""Honeywell""))"
Range("R4").FormulaR1C1 = "=RC[-2]+RC[-1]"

Range("P5").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""SPERIAN""))"
Range("Q5").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""SPERIAN""))"
Range("R5").FormulaR1C1 = "=RC[-2]+RC[-1]"

Range("P6").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""Starfield""))"
Range("Q6").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""Starfield""))"
Range("R6").FormulaR1C1 = "=RC[-2]+RC[-1]"

Range("R7").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

Merci pour votre aide.

Bonsoir,

pour ce qui est de la ligne 2, c'est du à l'écriture de votre formule qui est "relative"

Si vous êtes en Range("P3"), vous êtes en ligne 3 et avec R[-1] dans la formule vous "remonter" d'une ligne (le R vaut Row)

Donc les formules suivantes qui cible les lignes 4, 5 et 6 si vous faites R[-1] vous vous "retrouvez" en 3,4 et 5 !

Du coup cela ne commence pas à 2 !

Pour commencer à 2 partout il ne faut pas que vos formules soient "relatives" mais "Absolues"

@ bientôt

LouReeD

Bonjour LouReed,

du genre:

[P3].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""Innotex""))"
[Q3].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""Innotex""))"
[R3].FormulaR1C1 = "=RC[-2]+RC[-1]"

[P4].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""Honeywell""))"
[Q4].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""Honeywell""))"
[R4].FormulaR1C1 = "=RC[-2]+RC[-1]"

[P5].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""SPERIAN""))"
[Q5].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""SPERIAN""))"
[R5].FormulaR1C1 = "=RC[-2]+RC[-1]"

[P6].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""Starfield""))"
[Q6].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""Starfield""))"
[R6].FormulaR1C1 = "=RC[-2]+RC[-1]"

[R7].FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

Auriez vous une piste pour adapter la formule pour qu'elle calcul seulement les cellules non vide des colonne I et J?

Merci

Bonsoir,

si on test la cellule égale à Manteau, que faut-il éviter de compter si c'est égale à rien ?

Car Sommeprod fait la somme des produits et donc si une condition "par ligne" n'est pas respectée alors c'est égale à 0 donc cela ne change rien à la somme...

Il doit y avoir un truc qui m'échappe... La fatigue peut être ?

@ bientôt

LouReeD

Bonsoir Maître LouReed,

Serais-je en train de ne pas utiliser la bonne fonction?

En fait, ce calcul n'est qu'une portion du code. Il est intégré à une macro de mise en forme et de gestion de données.

J'extrais un rapport qui requiert un template spécifique, et d'une fois à l'autre le nombre de ligne n'est jamais pareil.

ici, je souhaite calculer le nombre de manteau et de pantalon par compagnie. Le SOMMEPROD m'est apparu comme une bonne solution...

La seule chose, c'est que la formule fonctionne pour une plage spécifique, alors que j'aimerais qu'elle fonctionne pour les lignes non vide des colonne I et J.

Voir onglet test qui donne le résultat de la macro MF_Liste_lundi_GR1

Bonjour LouReed,

Inspirer d'une trouvaille sur net, j'ai tenté ceci, mais ça renvoi à #NOM?

Sub MF_Liste_Lundi_GR1()
Dim i As Long
'Calcul

i = Range("I" & Rows.Count).End(xlUp).Row

'Renvoi à #NOM?
Range("I2").FormulaLocal = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Innotex""))"

'Fonctionne, mais pour une place spécifique
[J2].FormulaR1C1 = "=SUMPRODUCT((R7C9:R400C9=""PANTALON"")*(R7C10:R400C10=""Innotex""))"

End Sub

Merci

A+

Bonjour,

C'est peut être du à votre format locale...

Votre Excel est en français non?

Enlever le "local" de la ligne du dessus. Alors Excel transformera de lui même l'instruction dommprod anglaise en français... Peut être est ce cela...

@bientôt

LouReeD

Bonjour LouReed,

Que pensez-vous de ceci? J'imagine qu'il y a place à l'amélioration du code, mais pour moi, c'est le plus loin que je puisse aller!

Sub MF_Liste_Lundi_GR1()
Dim i As Long

' En-tête grille de calcul
[I1] = "Manteau"
[J1] = "Pantalon"
[H2] = "Innotex"
[H3] = "Honeywell"
[H4] = "SPERIAN"
[H5] = "Starfield"
[J6] = "Total"

With Range("I1:J1")
     'Range("J6:K6")
    .Font.Bold = True
    .Interior.ColorIndex = 1
    .Font.ColorIndex = 2
End With

With Range("H2:H5")
    .Interior.ColorIndex = 15
End With

With Range("I5:K5").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
End With

'Calcul

i = Range("I" & Rows.Count).End(xlUp).Row
i = Range("j" & Rows.Count).End(xlUp).Row

Range("I2").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Innotex""))"
Range("J2").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""Innotex""))"
[K2].FormulaR1C1 = "=RC[-2]+RC[-1]"

Range("I3").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Honeywell""))"
Range("J3").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""Honeywell""))"
[K3].FormulaR1C1 = "=RC[-2]+RC[-1]"

Range("I4").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""SPERIAN""))"
Range("J4").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""SPERIAN""))"
[K4].FormulaR1C1 = "=RC[-2]+RC[-1]"

Range("I5").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Starfield""))"
Range("J5").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""Starfield""))"
[K5].FormulaR1C1 = "=RC[-2]+RC[-1]"

[K6].FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

End Sub

Merci

Bonsoir,

Je ne comprends pas : le "i" est égal à la dernière cellule de la colonne I ou de la colonne J ?

Vous calculez les deux mais du coup la dernière valeur remplace la première... Ne manque t il pas un test ?

Si je comprend bien le fait de retirer "local" à résolu le problème ?

@bientôt

LouReeD

Bonjour LouReed,

ouille, je ne sais pas quoi vous répondre, j'ai mis les deux ne sachant pas ce qu'il fallait mettre. Donc, si je comprends bien, un seul i pour la Collone I devrait suffire à la formule? Et s'il manque un test, je ne sais comment m'y prendre!

Effectivement, le retrait de Local a résout la mauvaise référence.

Merci et à bientôt

Pour être sûr de faire tous les tests il faut prendre la plus grande valeur des deux calculs, enfin si je comprend bien... Donc :

I = dernière cellule pleine de la colonne I,

Puis

Si dernière cellule pleine de J > à I alors i=dernière cellule pleine de J

@bientôt

LouReeD

Rechercher des sujets similaires à "sommeprod vba plage vide"