VBA: SUMPRODUCT avec variables

Bonjour le forum,

Je me prends la tête avec ceci:

Dans un onglet "IMPORT", j'ai un tableau avec des colonnes qui peuvent varier de position:

Pour l'exemple, je cherche à compter en "A10" d'une autre feuille, le nombre de "Nom" en fonction d'un "Type".

De ce fait comment appliquer en VBA une instruction du style:

Range("A10") = ("=SUMPRODUCT((B:B=""Nom 4"")*(D:D=""C14""))")

Comme je ne maitrise absolument pas et ca se voit) ,

j'y vais au "petit bonheur" et ai essayé de passer par cette méthode: Déterminer les colonnes en fonction de la ligne de titre:

Sub atester()
Dim col_Nom As Range
Dim col_Type As Range
  Set col_Nom = Sheets("IMPORT").Cells.Find(what:="Nom", LookIn:=xlValues, lookat:=xlWhole)
  Set col_Type = Sheets("IMPORT").Cells.Find(what:="Type", LookIn:=xlValues, lookat:=xlWhole)
      colonne_Nom = Left$(Columns(col_Nom.Column).Address(0, 0), Len(CStr(Columns(col_Nom.Column).Column))) 'Transforme le n° de colonne en lettre
    colonne_Type = Left$(Columns(col_Type.Column).Address(0, 0), Len(CStr(Columns(col_Type.Column).Column))) 'Transforme le n° de colonne en lettre
      Exit Sub
End Sub

Mais je n'arrive pas à incorporer mes variables de colonnes, donc de remplacer B:B par colonne_Nom et D:D par colonne_Type:

    Range("A10") = ("=SUMPRODUCT((B:B=""Nom 4"")*(D:D=""C14""))")

Si une bonne âme a une proposition, je prends avec plaisir

Merci pour votre aide et vous souhaite un excellent week-end.

Bonjour,

Une proposition à adapter

Sub atester()
Dim col_Nom As Range, col_Type As Range, Plage_Nom As Range, Plage_Type As Range
Dim NomX As String, TypeX As String
Dim NbX As Integer
    NomX = InputBox("Quel est le nom recherché ?")
    TypeX = InputBox("Quel est le type recherché ?")
    With Worksheets("IMPORT")
        Set col_Nom = .Rows(1).Find(what:="Nom", LookIn:=xlValues, lookat:=xlWhole)
        If Not col_Nom Is Nothing Then
            Set Plage_Nom = .Range(col_Nom, col_Nom.End(xlDown))
            Set col_Type = .Rows(1).Find(what:="Type", LookIn:=xlValues, lookat:=xlWhole)
            If Not col_Type Is Nothing Then
                Set Plage_Type = .Range(col_Type, col_Type.End(xlDown))
                NbX = Application.CountIfs(Plage_Nom, NomX, Plage_Type, TypeX)
                MsgBox "Nombre de lignes correspondant à ces deux critères : " & NbX
            End If
        End If
    End With
End Sub

A+

Bonjour,

Lorsque le problème ressemble potentiellement à un casse-tête ... un fichier joint permet de dédramatiser les choses ...

Edit : Salut Frangy ... bravo ... car coder sans support ... c'est fort ...!!!

Bonjour James007,

Non, ce n'est pas fort, c'est simplement que j'ai préféré créer le classeur plutôt que répéter encore et encore qu'il faut joindre un fichier.

C'est une faiblesse passagère, je vais me reprendre

A+

frangy a écrit :

Bonjour James007,

Non, ce n'est pas fort, c'est simplement que j'ai préféré créer le classeur plutôt que répéter encore et encore qu'il faut joindre un fichier.

C'est une faiblesse passagère, je vais me reprendre

A+

Salut Frangy,

Je constate que tu as depuis très longtemps quitté le registre de l'aide ... pour plonger dans le dévouement le plus total ...!!!

Hello Frangy, hello James007,

Effectivement sans fichier c'est pas facile...

Certains forums sont allergiques aux fichiers joints, d'autre moins

Veuillez m'excuser, cela ne se reproduira plus, promis !

Frangy je te remercie d'autant plus pour ton aide qui fonctionne pour autant que toutes les lignes de la colonne soient remplies.

Malheureusement j'ai des lignes vides dans mes colonnes ce qui provoque une erreur de compatibilité de type sous:

NbX = Application.CountIfs(Plage_Nom, NomX, Plage_Type, TypeX)  

Cette fois je joint

Merci encore.

Same player, shoot again

A+

Frangy tu es trop bon

Je m'approche du Gral.... Au risque de m'en ramasser encore une (rapport à ton smiley):

J'ai encore juste un résultat faussé si la colonne Nom n'est pas complète ...

En effet le "countifs" s’arrête à la première cellule vide.

En fait dans mon tableau, la seule colonne qui est à coup sur complète est la colonne "ID".

J'essaie de comprendre le détail de ton code afin corriger le tir en prenant la colonne "ID" pour définir la plage mais ca aurait été trop facile

Merci pour ton aide et le temps que tu me consacres.

Pour calculer le nombre de lignes à prendre ne compte j'ai utilisé :

NbLig = col_ID.End(xlDown).Row - 1

Tu peux visualiser le résultat obtenu avec cette méthode en sélectionnant l'en-tête "ID" puis en appuyant sur fin puis flèche bas. Tu te positionnes ainsi sur la dernière cellule renseignée dans la colonne "ID". Il suffit alors de relever le numéro de ligne pour obtenir le nombre de ligne de données (-1 pour l'en-tête).

La hauteur de la plage étant déterminée, les deux plages "Nom" et "Type" sont définies avec cette même dimension.

Sub atester()
Dim col_Nom As Range, col_Type As Range, Plage_Nom As Range, Plage_Type As Range, col_ID As Range, Plage_ID As Range
Dim NomX As String, TypeX As String
Dim NbX As Integer
Dim NbLig As Long
    NomX = "Nom 4"
    TypeX = "C14"
    With Worksheets("IMPORT")
        'La variable col_ID correspond à la cellule d'en-tête "ID"
        Set col_ID = .Rows(1).Find(what:="ID", LookIn:=xlValues, lookat:=xlWhole)
        If Not col_ID Is Nothing Then
            'Nombre de lignes de données dans la colonne "ID"
            NbLig = col_ID.End(xlDown).Row - 1
            'La variable col_Nom correspond à la cellule d'en-tête "Nom"
            Set col_Nom = .Rows(1).Find(what:="Nom", LookIn:=xlValues, lookat:=xlWhole)
            If Not col_Nom Is Nothing Then
                'La variable Plage_Nom représente la plage des noms.
                'La hauteur de cette plage est égale à celle des ID
                Set Plage_Nom = col_Nom.Offset(1).Resize(NbLig)
                'La variable col_Type correspond à la cellule d'en-tête "Type"
                Set col_Type = .Rows(1).Find(what:="Type", LookIn:=xlValues, lookat:=xlWhole)
                If Not col_Type Is Nothing Then
                    'La variable Plage_Type représente la plage des types.
                    'La hauteur de cette plage est égale à celle des ID
                    Set Plage_Type = col_Type.Offset(1).Resize(NbLig)
                    'Nombre de lignes satisfaisant aux 2 critères
                    NbX = Application.CountIfs(Plage_Nom, NomX, Plage_Type, TypeX)
                    'Affichage du résultat dans Test!A10
                    Worksheets("TEST").Range("A10") = "Nombre de lignes correspondant à ces deux critères : " & NbX
                End If
            End If
        End If
    End With
End Sub

A+

Je suis toujours sur le impressionné :

1: Par le niveau de connaissance de l'équipe

2: La gentillesse et le temps que vous consacrez à la "bleusaille" que nous sommes.

Pour ca MERCI pour ton aide et surtout tes explications plus que détaillées !!!! Très sympa.

J'ai une dernière question (non pas la tête...) : Pourquoi es-tu partis sur la solution "COUNTIFS" plutôt que "SUMPRODUCT" ?

Encore merci pour tout.

Pourquoi faire simple quand on peut faire compliqué ?

La fonction COUNTIFS est celle qui correspond le mieux à ton questionnement :

"Je souhaite compter les lignes d'une plage qui répondent à plusieurs critères".

A+