Evaluate avec Sumproduct ne fonctionne pas

Bonjour à tous

J'essaie depuis 2 jours de me dépatouiller d'une question existentielle de vba

J'ai un applicatif de gestion de réservations depuis lequel j'extrait des analyses statististiques

Sur une sheet "newbdd", j'ai des tableaux structurés pour gérer mes données de base.
Sur une autre sheet "tcd", j'ai des données consolidées qui me servent de base à mes analyses graphiques et autres consolidations.

Dans newbdd, j'ai une table "reservations" contenant des dates
Dans tcd, j'ai une table structurée "statsweek" dans laquelle j'agrège certaines infos à la semaine iso.

Une partie du code simplifié

Sub MajStatsHebdos()    'Mise à jour des stats par semaine

Dim dateMin As Date         'date min
Dim dateMax As Date         'date max
Dim nosemMin As Integer     'semaine ISO de la date min
Dim nosemMax As Integer     'semaine ISO de la date max
Dim nbsem As Integer        'nb de semaine pour la boucle de traitement d'écriture des lignes de stats (une par semaine)

Dim i As Integer

Dim NewStatWeekRow As ListRow

    Set reservationsTable = Worksheets("newbdd").ListObjects("reservations")                'variable globale / sheet('newbdd')
    Set StatsWeekTable = Worksheets("tcd").ListObjects("statsweek")                         'tableau structuré dans la worksheet

    dateMin = Application.Min(reservationsTable.ListColumns("dateresa").DataBodyRange)      'setting date min / variable globale
    dateMax = Application.Max(reservationsTable.ListColumns("datesejour").DataBodyRange)    'setting date max / variable globale

    nosemMin = Application.WorksheetFunction.IsoWeekNum(dateMin)                            'setting semaine ISO date min
    nosemMax = Application.WorksheetFunction.IsoWeekNum(dateMax)                            'setting semaine ISO date max
    nbsem = nosemMax - nosemMin                                                             'nb de semaine pour la boucle de traitement d'écriture

    '
    'Boucle de traitement d'écriture des enregistrements consolidés par semaine dans la table statsweek
    '
    For i = 0 To 1  'nbsem

        Set NewStatWeekRow = StatsWeekTable.ListRows.Add

        With NewStatWeekRow
            .Range(1) = nosemMin + i    'n° de la semaine
            .Range(2).FormulaLocal = "=SOMMEPROD((NO.SEMAINE.ISO(reservations[dateresa])=[@Semaines])*(reservations[noresa]<>""""))"    'nb résa ; avec [@Semaines] = Range(1)
            .Range(3).FormulaLocal = "=SOMMEPROD((NO.SEMAINE.ISO(reservations[datesejour])=[@Semaines])*(reservations[noresa]<>""""))"  ' nb séjour
            .Range(4).FormulaLocal = "=SI([@Semaines]<>"""";SOMMEPROD(reservations[mnt]*(reservations[rglmt]>0)*(NO.SEMAINE.ISO(reservations[rglmt])=[@Semaines]));"""")"   'encaissé
        End With

    Next i

End Sub

Tout fonctionne très bien mais FormulaLocal, c'est lourd, vilain et pas très utile, autant tirer une formule existante.

Je cherche donc à remplacer cela par le résultat des formules.

J'ai essayé différentes combinaisons et déclinaisons sans succès. Exemple sur le .Range(2)

'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[dateresa]) =[@Semaines])*(reservations[noresa] <> ""))")    'nb résa ; [@Semaines] = Range(1) ==> #VALEUR
'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[dateresa]) =[@Semaines])*(reservations[noresa] <> """"))")    'nb résa ; [@Semaines] = Range(1) ==> #VALEUR

'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[dateresa]) = """ & [@Semaines] & """)*(reservations[noresa] <> ""))")    'nb résa ; [@Semaines] = Range(1)    ==> incompatibilité de type
'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[dateresa]) = """ & [@Semaines] & """)*(reservations[noresa] <> """"))")    'nb résa ; [@Semaines] = Range(1)    ==> incompatibilité de type

'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(coldateresa) =[@Semaines])*(colnoresa <> ""))")    'nb résa ; [@Semaines] = Range(1) ==> #VALEUR
'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(coldateresa) =[@Semaines])*(colnoresa <> """"))")    'nb résa ; [@Semaines] = Range(1) ==> #VALEUR

'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(coldateresa) = """ & [@Semaines] & """)*(colnoresa <> ""))")    'nb résa ; [@Semaines] = Range(1)    ==> incompatibilité de type
'.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(coldateresa) = """ & [@Semaines] & """)*(colnoresa <> """"))")    'nb résa ; [@Semaines] = Range(1)    ==> incompatibilité de type

Dans ces cas, j'ai soit utilisé les réfs directes reservations[dateresa] et reservations[noresa], soit je les ai remplacées par des Range coldateresa et colnoresa définies comme suit

Dim coldateresa As Range    'colonne des dates de resas du tableau structuré réservations (variable globale)
Dim colnoresa As Range      'colonne des n° de resas du tableau structuré réservations (variable globale)

    Set coldateresa = reservationsTable.ListColumns("dateresa").DataBodyRange               'setting des dates de resas / variable globale
    Set colnoresa = reservationsTable.ListColumns("noresa").DataBodyRange                   'setting des n° de resas / variable globale

pour rendre le code plus lisible.

J'ai aussi essayé les WorksheetFunction.Sumproduct((
et aussi les formulations Range(2).Value = SUMPRODUCT((Application.WorksheetFunction.ISOWEEKNUM,
quoiqu'il en soit, j'obtiens soit des #VALEUR, soit des incompatibilités de type.

Est-ce que cela signifie que vba n'accepte pas des imbrications de WorksheetFunction ?
Est-ce que vba ne permet pas de travailler des sumproduct sur des Range ?
Est-ce que quelqu'un voit ce qu'il y a d'évident et à travers de quoi je passe ?
Est-ce que je risque d'être obligé de créer une colonne de plus dans ma table "reservations" pour faire le calcul d'isoweeknum au préalable pour travailler sur elle directement ?

Dans l'attente d'un peu de lumière dans tout ce flou :D

Bonjour thiefer

Tout fonctionne très bien mais FormulaLocal, c'est lourd, vilain et pas très utile

A priori la preuve que si que cette instruction est utile

Ceci dit, si je comprends bien les formules, un NB.SI.ENS() suffirait amplement et serait moins gourmand en ressource

Je ne faisais que passer

Bonjour BrunoM45

Alors oui, c'est sûr, ça peut-être utile :D
Mais dans mon cas, si je fais l'impasse, l'incrémentation d'une nouvelle ligne dans mon tableau avec une info sur une zone adjacente (Range(1)) va "tirer" la formule d'elle-même depuis la ligne précédente sans que je n'ai rien à demander à vba.

Ce qui me dérange, c'est que la méthode Formula rend le fichier bcp plus lourd et surtout bcp plus lent.

Sinon, pour nb.si.ens, c'est effectivement en soit une sous-fonction de sommeprod.
En revanche, même si j'écris directement dans ma cellule ma formule en dur avec, nb.si.ens est plus limité. Il semble ne pas vouloir accepter de travailler sur une colonne de valeurs non existante déjà physiquement dans le tableau (no.semaine.iso), contrairement à sommeprod.

C'est pour cela que je ne comprends pas pourquoi si j'y arrive à la mimine dans ma cellule avec sommeprod, qu'est-ce qui merdouille à le recalculer en vba ?...

Une piste semble vouloir enfin se dessiner !!!
Le problème viendrait de la référence [@Semaines] qui est le nom de l'entête de la deuxième colonne de mon tableau structuré statsweek que je suis en train d'alimenter.

En effet, j'ai repris la seconde ligne et j'ai remplacé la référence [@Semaines] par 17, pour dire à vba de calculer toutes les résas enregistrées en semaine 17

            '.Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[dateresa]) =[@Semaines])*(reservations[noresa] <> ""))")    'nb résa ; [@Semaines] = Range(1) ==> #VALEUR
            .Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[dateresa]) = 17)*(reservations[noresa] <> """"))")    'nb résa ; [@Semaines] = Range(1) ==> #VALEUR

Ca fonctionne, le calcul est juste.

Il va maintenant falloir comprendre pourquoi il ne veut pas de mon entête de colonne et comment me débrouiller pour la variabiliser...

Si quelqu'un était intéressé, j'ai trouvé une solution, la piste que j'ai suivie était bonne, j'ai finalement décidé de ne pas utiliser en l'état le nom de la ligne d'entête

Le code devient le suivant

        With NewStatWeekRow
            noSem = nosemMin + i
            .Range(1) = noSem    'n° de la semaine
            .Range(2).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[dateresa]) = " & noSem & ")*(reservations[noresa] <> """"))")   'nb résas
            .Range(3).Value = Application.Evaluate("SUMPRODUCT((ISOWEEKNUM(reservations[datesejour])= " & noSem & ")*(reservations[noresa] <> """"))")    ' nb séjours
            .Range(4).Value = Application.Evaluate("IF(" & noSem & " <> """" , SUMPRODUCT(reservations[mnt]*(reservations[rglmt]>0)*(ISOWEEKNUM(reservations[rglmt])= " & noSem & ")),"""")")  ' encaissé
            .Range(5).Value = Application.Evaluate("LET(Janv1,DATE(2023,1,1),Jour1,WEEKDAY(DATE(2023,1,1),2),Janv1+IF(Jour1<5,(" & noSem & "-1)*7," & noSem & "*7)-Jour1+1)")  ' lundi
            .Range(6).Value = Application.Evaluate("LET(Janv1,DATE(2023,1,1),Jour7,WEEKDAY(DATE(2023,1,1),2),Janv1+IF(Jour7<5,(" & noSem & "-1)*7," & noSem & "*7)-Jour7+7)")  ' dimanche
        End With

Et question perfs, ça n'a vraiment plus rien à voir, c'est de l'immédiat à présent

Re,

thiefer a écrit :

Bonjour BrunoM45

Alors oui, c'est sûr, ça peut-être utile :D
Mais dans mon cas, si je fais l'impasse, l'incrémentation d'une nouvelle ligne dans mon tableau avec une info sur une zone adjacente (Range(1)) va "tirer" la formule d'elle-même depuis la ligne précédente sans que je n'ai rien à demander à vba.

Ce qui me dérange, c'est que la méthode Formula rend le fichier bcp plus lourd et surtout bcp plus lent.

Non, ce n'est pas formula (qu'elle soit local ou R1C1) qui rend le fichier beaucoup plus lourd et plus lent,
c'est cette fameuse formule matricielle de SOMMEPROD()

Ceci dit, il serait préférable et même recommandé de joindre un fichier anonymisé à votre demande
pour que l'on puisse mieux comprendre et mieux vous répondre

A+