Sommeprod en VBA

[ADV974]

Bonjour,

Je suis nouvelle sur le forum et plutôt novice en vba. Je souhaite indiquer en cellule G9 le nombre de cellules contenant "Aucune" et ce pour la personne sélectionnée en B1. Pour cela, je pensais passer par une fonction sommeprod en vba. Voici le code que j'ai réalisé et qui... ne fonctionne pas (affiche 0 au lieu de 1):

Sub Macro3()
'
'Définit la plage contenant le nom des personnes
Set Plage1 = Workbooks("CAPITAL_NON_AMORTI01APR12.XLS").Sheets("EXI_PAS_AMO").Range("$I$1:$I$1000")

'Définit la plage avec les cellules pouvant contenir "Aucune"
Set Plage2 = Workbooks("CAPITAL_NON_AMORTI01APR12.XLS").Sheets("EXI_PAS_AMO").Range("$J$1:$J$1000")

'Définit la cellule contenant le nom de la personne sélectionnée
NOM = Workbooks("2012_REPORT_CTRL_COLL_NEGO.xls").Sheets("SYNTHESE").Range("B1")

Windows("2012_REPORT_CTRL_COLL_NEGO.xls").Activate

'C'est là que réside tout le problème hélas
Sheets("SYNTHESE").Range("G9").Value = Evaluate("=sumproduct((Plage1=""" & NOM & """)*(Plage2=" & """Aucune""" & "))")

End Sub

Voilà! J'ai longtemps cherché la réponse dans les différents forums et testé un certain nombre de syntaxes...j'avoue que je désespère! Si une bonne âme veut bien se pencher sur mon problème ce serait super!

Merci beaucoup à tous!

 

[pijaku]

Re : Sommeprod en VBA

Bonjour,

Essaye ceci :
- supprimer le signe = devant SUMPRODUCT
- "sortir" les variables des guillemets
- utiliser l'adresse des range et non pas les range eux mêmes.
Soit :

Sheets("SYNTHESE").Range("G9").Value = Evaluate("SUMPRODUCT((" & Plage1.Address & "=""" & NOM & """)*(" & Plage2.Address & "=" & """Aucune""" & "))")

 

[Staple1600]

Re : Sommeprod en VBA

Bonjour à tous

ADV974 (La bienvenue sur le forum)
Et il est où le fichier exemple si on veut tester les propositions de réponses qu'on peut être susceptible de t'apporter?
Sur ton disque dur.

Ça nous fait une belle jambe...

PS; je parierai mon caleçon fétiche que toi non plus tu n'as pas lu la charte du forum...

 

[ADV974]

Re : Sommeprod en VBA

Bonjour Staple1600

Oui tu peux encore conserver ton caleçon fétiche, je n'avais pas lu la charte du forum mais je me suis empressée de le faire (si si je t'assure). J'ai rajouté mes fichiers exemple en espérant que cela puisse aider.

Encore toutes mes excuses pour mes maladresses

 

[ADV974]

Re : Sommeprod en VBA

Bonjour Pijaku et merci pour la rapidité de ta réponse

J'ai testé ton code mais cela affiche toujours 0 au lieu de 1 normalement. Néanmoins, grâce à toi, j'ai un peu progressé dans la compréhension de la syntaxe.

Encore merci pour ton aide.

 

[pijaku]

Re : Sommeprod en VBA

Re-
Bonjour à Staple1600,

Lors du calcul d'une fonction SOMMEPROD, il faut que tu sois sur le classeur concerné.
Tu l'actives vite fait et le tour est joué. Je ne saurais également trop te conseiller de systématiquement déclarer tes variables :

Sub Macro3()
Dim NOM As String, Plage1 As Range, Plage2 As Range, Resultat As Long

With Workbooks("2012_REPORT_CTRL_COLL_NEGO.xls").Sheets("SYNTHESE")
    NOM = .Range("B1")
End With
With Workbooks("CAPITAL_NON_AMORTI01APR12.XLS")
    .Activate
    Set Plage1 = .Sheets("EXI_PAS_AMO").Range("$I$1:$I$1000") 'Plage avec le Nom des collaborateurs
    Set Plage2 = .Sheets("EXI_PAS_AMO").Range("$J$1:$J$1000") 'Plage  avec le Nb de dossiers conformes par requête
    Resultat = Evaluate("SUMPRODUCT((" & Plage1.Address & "=""" & NOM & """)*(" & Plage2.Address & "=" & """AUCUNE""" & "))")
End With
With Workbooks("2012_REPORT_CTRL_COLL_NEGO.xls").Sheets("SYNTHESE")
    .Range("G9") = Resultat
    .Activate
End With
End Sub

Et soit également super précis dans la casse : AUCUNE pas aucune, SUMPRODUCT pas sumproduct...

 

[Staple1600]

Re : Sommeprod en VBA

Re


Merci pour la PJ.

Maintenant je vais pouvoir tester ce que j'avais en tête.

Sub a()
Dim NOM$, Plage1 As Range, Plage2 As Range
Dim WBK_S As Workbook, WBK_D As Workbook
Set WBK_S = Workbooks("CAPITAL_NON_AMORTI01APR12.XLS")
Set WBK_D = ThisWorkbook

With WBK_S.Sheets("EXI_PAS_AMO")
Set Plage1 = .Range("$I$1:$I$1000"): Set Plage2 = .Range("$J$1:$IJ$1000")
End With

With WBK_D.Sheets("SYNTHESE")
NOM = .[B1]
.[G9] = Evaluate("SUMPRODUCT((" & Plage1.Address & "=""" & NOM & """)*(" & Plage2.Address & "=" & """AUCUNE""" & "))")
End With

End Sub

Par contre avec tes fichiers tels quels, cela renvoie 0

EDITION: Effectivement l'Activate change la donne

Sub a()
Dim NOM$, Plage1 As Range, Plage2 As Range, Tmp$
Dim WBK_S As Workbook, WBK_D As Workbook
Set WBK_S = Workbooks("CAPITAL_NON_AMORTI01APR12.XLS")
Set WBK_D = ThisWorkbook
NOM = WBK_D.Sheets("SYNTHESE").[B1]
With WBK_S.Sheets("EXI_PAS_AMO")
.Activate
Set Plage1 = .Range("$I$1:$I$1000"): Set Plage2 = .Range("$J$1:$IJ$1000")
Tmp = Evaluate("SUMPRODUCT((" & Plage1.Address & "=""" & NOM & """)*(" & Plage2.Address & "=" & """AUCUNE""" & "))")
End With
WBK_D.Sheets("SYNTHESE").[G9] = Tmp
End Sub

PS1: j'espère que les noms qui apparaissent dans ta liste déroulante de la feuille SYNTHESE ne sont pas confidentiels.
Ou que les personnes concernées ne seront pas dérangées de voir leur nom se trimbaler sur le net
PS2: C'est pour cela que la charte conseille de poster des fichiers anonymisés.

 

[ADV974]

Re : Sommeprod en VBA

Pijaku

Cela fonctionne à merveille! Je conserve précieusement ton code ainsi que tes conseils fort judicieux!

Merci infiniment

 

[Staple1600]

Re : Sommeprod en VBA

Re


N'oublies pas d'anonymiser ton classeur tout de même.

(et accessoirement de jeter un œil sur le code VBA de mon précédent message )

 

[pijaku]

Re : Sommeprod en VBA

@ Staple1600,
Je profites de ta présence pour une petite question subsidiaire (et qui accessoirement n'a rien à voir avec le sujet) :
Que signifie le $ ajouté en fin de nom de variable? A quoi ça peux bien servir? Pourquoi et quand l'utiliser? Peux t'on l'utiliser en spécifiant un type (Dim Tmp$ As Long)
exemple : Dim NOM$, Plage1 As Range, Plage2 As Range, Tmp$

 

[Staple1600]

Re : Sommeprod en VBA

Re

pikaju:
Le $ est dans la faq du forum et dans l'aide VBA (touche F1)
Mais comme ton code fonctionnait mieux que le mien dès le départ, je te réponds volontiers

Dim NOM As String
peut se raccourcir en
Dim NOM$

c'est ce qu'on appelle: (extrait de l'aide VBA)
Le caractère de déclaration de type String est le signe $.

 

[pijaku]

Re : Sommeprod en VBA

Merci Staple.

Pour ma défense, f1 : déjà fait... "$" ne me renvoie aucun résultat, "Dim $" me renvoie les déclarations de variables sans allusions aucunes à ce "$"...

L'extrait de la Faq, j'l'avais déjà lu, mais je n'ai jamais fait le rapprochement entre ($) et ce que je voulais... faut dire que je ne suis pas réveillé tous les jours, et que ça n'est pas très explicite non plus pour les néophites.

Donc merci, je suis encore moins bête d'une petiote longueur...

A bientôt

 

[ADV974]

Re : Sommeprod en VBA

Re Staple1600

J'ai anonymisé mon classeur, merci beaucoup pour l'alerte, je n'avais pas fais attention (idiote que je suis )

Merci beaucoup pour ton aide sur mon problème. Je me coucherai moins bête

Bonne journée

 

[Staple1600]

Re : Sommeprod en VBA

Re

Pour pijaku
Si tu tapes String dans l'aide VBA, tu tomberas sur ceci

String, type de données

Il existe deux types de chaînes : les chaînes de longueur variable et les chaînes de longueur fixe.

• Les chaînes de longueur variable peuvent contenir environ 2 milliards (2^31) de caractères.
• Les chaînes de longueur fixe peuvent contenir de 1 à environ 64 Ko (2^16) de caractères. Note Une chaîne de longueur fixe déclarée comme Public ne peut pas être utilisée dans les modules de classe.

Les codes de caractères de type String sont compris entre 0 et 255. Les 128 premiers caractères (0 à 127) du jeu de caractères correspondent aux lettres et symboles d'un clavier américain standard. Ces 128 caractères sont identiques à ceux du jeu de caractères ASCII. Les 128 caractères suivants (128 à 255) représentent des caractères spéciaux, comme les lettres de certains alphabets, les accents, les symboles monétaires et les fractions. Le caractère de déclaration de type String est le signe $.

Et c'est à la dernière ligne que le $ apparaissait

 

[pijaku]

Re : Sommeprod en VBA

Bonjour,

Merci Staple.

A bientôt