PB de concatenation de texte pour calcul formule complexe

Bonjour à toutes et à tous.

Je suis bloqué sur un pb de concaténation en texte de morceaux de formule que je veux ensuite calculer.

Mon objectif est le suivant : je dois effectuer un sommeprod variable selon les lignes et surchargé par un dernier critère de sommeprod variable lui aussi, mais selon les colonnes

Exemple de formule complète

=SOMMEPROD((INDIRECT(U18))*(INDIRECT(W18))*(INDIRECT(Y18)=Z18)*(INDIRECT(AB18)=AC18)*(INDIRECT(T18)<>"")*(INDIRECT(T11)=$J$8))

Cette formule fonctionne correctement

Dans mon mécanisme,

La première cellule , que je vais appeler A18, contient plusieurs critères de calcul pour mon SOMMEPROD

(INDIRECT(U18))*(INDIRECT(W18))*(INDIRECT(Y18)=Z18)*(INDIRECT(AB18)=AC18)*(INDIRECT(T18)<>"")

La seconde cellule, que je vais appeler B18, de filtre complémentaire contient, pour exemple:

(INDIRECT(T11)=$J$8)

Un concaténer, en C18 pour exemple, de type =CONCATENER("SOMMEPROD(";A18;"*";B18;")") ou ="SOMMEPROD("&A18&"*"&B18&")"

- Me donne bien ma formule cible au format texte, sans le "="

- Me permet ensuite de tester son calcul par la fonction EVAL (que j'ai posé en VBA)

mais sans succès, en direct comme en passant par un concaténer intermédiaire

=EVAL("SOMMEPROD("&A18&"*"&B18&")") me donne #Nom

=EVAL(C18) me donne juste la formule, sans la calculer et sans le =

(2 VBA EVAL testés : Eval = Evaluate(Ref) et EvaluateString = Application.Caller.Parent.Evaluate(Ref))

La question que je vous soumets est la suivante, du fait de mon incompétence crasse en VBA :

- La petite fonction EVAL que j'ai trouvée suffit-elle pour ce genre de formule complexe (elle fonctionne bien pour des calculs basiques)

- En alternative, une fonctionnalité VBA me permet-elle, sur la base de ces 2 contenus texte A18 et B18 de demander le calcul du SOMMEPROD dans VBA, sans devoir reprogrammer cette fonction SOMMEPROD dans VBA ?

Point important : idéalement, je ne souhaite pas utiliser de calcul matriciel car ce fichier sera utilisé/mis à jour par trop de personnes

En espérant que ma question est claire, et mon énoncé suffisant pour vous permettre de me fournir des pistes !

Bon début de semaine dans tous les cas !!

Achille

En complément, testé aussi :

Function SumPrd(ValSrc As Range, ValAdd As Range)
    strFormula = "=SOMMEPROD(" & ValSrc.Value & "*" & ValAdd.Value & ")"
    'SumPrd = strFormula me donne la bonne formule si je la copie-colle : calcul OK
    SumPrd = Application.Evaluate(strFormula)
End Function

Si je pointe sur A18 et B18 : formule bonne, mais erreurs si je veux faire le calcul : #NOM?

Si çà peut aider ??

En complément, ai vu que INDIRECT semblait poser problème en VBA => a évalué ajout d'une fonction INDIRVBA:

Public Function INDIRVBA(ref_text As String)
    INDIRVBA = Range(ref_text)
End Function

et ai modifié mes contenus de A18 et B18 pour remplacer INDIRECT par INDIRVBA, sans succès

En dernière intention j'ai remplacé mes INDIRECT par leur valeur cible associée :

=SOMMEPROD((Inv_test!B24:B28)*(Inv_test!K24:K28)*(Inv_test!L24:L28="PROD")*(Inv_test!M24:M28="Cible")*(Inv_test!C24:C28<>"")*(Inv_test!C24:C28="CREA"))

Sans meilleur résultat dans mon VBA

Ai vu que potentiellement il y avait un pb sur le fait de faire référence à un autre onglet :

- Dois-je le référencer pour permettre de faire mes recherches sur cet onglet "Inv_Test" tout en affichant le résultat dans mon onglet courant 'Inv_Result".

- dois-je pour cela le passer en paramètre additionnel à la fonction sur laquelle je travaille ?

Function SumPrd(ValSrc As Range, ValAdd As Range)
    strFormula = "=SOMMEPROD(" & ValSrc.Value & "*" & ValAdd.Value & ")"
    'SumPrd = strFormula me donne la bonne formule si je la copie-colle : calcul OK
    SumPrd = Evaluate(strFormula)
End Function

... Et comment ?

Je continue a gratter, mais si quelqu'un se récite la formule en se rasant le matin...je suis preneur!

Bonjour,

Sauf erreur, je crois Qu'Evaluate fait office d'Indirect VBA également (mais je n'ai jamais testé personnellement). A voir :

Function SumPrd(ValSrc, ValAdd)
    strFormula = "SUMPRODUCT(" & evaluate(ValSrc) & "*" & evaluate(ValAdd) & ")"
    SumPrd = Evaluate(strFormula)
End Function

Il faut utiliser le nom anglais des fonctions SOMMEPROD = SUMPRODUCT. Si ça bloque, essayez en remplacant les 2 premiers evaluate par la fonction INDIRVBA.

Sinon, ça semble bien compliqué comme mise en oeuvre pour faire un sommeprod et pas très utile à moins de conditionner et variabiliser l'utilisation des plages servant au calcul.

Cdlt,

Merci pour ta réponse, 3GB.

Je vais tester finement et faire un retour. sur les premiers tests KO, mais je vais abaisser le niveau de complexité pour tenter de voir ce qui bloque.

Concernant le use-case : effectivement mon besoin est tordu, mais

- J'ai plusieurs centaines de lignes avec des sommeprod multiparametres, et nb/type de paramètres variable : premier sommeprod presque à la ligne

- Je dois ensuite challenger ces sommesprod avec des filtres complémentaires (total, existant, nouveaux, suppression)

Cela explique (j’espère) pourquoi je cherche un mécanisme qui permet des ajouts de lignes en abaissant les risques : seul le premier sommeprod serait à bien checker

Je reviens avec des réponse sous 36h

Cdlt,

Bonjour, Ce retour pour Vous dire que le pb est résolu :

Rappel concatenation de SOMMEPROD avec du indirect dans tous les sens et sur un autre onglet.

Merci à 3GB pour ses pistes.

Après avoir résolu mon pb, j'ai du trouver un mécanisme de refresh de mes formules.

En synthèse :

2 fonctions

Function SumProdVar1(ValSrc, ValAdd, Optional VolatileParameter As Variant)
    strFormula = "SUMPRODUCT(" & ValSrc.Value & "*" & ValAdd.Value & ")"
    SumProdVar1 = Evaluate(strFormula)
End Function

Public Function INDIRVBA(ref_text As String)
    INDIRVBA = Range(ref_text)
End Function

Application : Avec 2 chaines du type (en C149 et C150) (avec indirect remplacé par INDIRVBA) :

(INDIRVBA(B76))*(INDIRVBA(D76)=I76)*(INDIRVBA(E76)=J76)*(INDIRVBA(F76)=K76)

(INDIRVBA(C76)=L76)

Et un appel via : =SumProdVar1(C149;C150;MAINTENANT())

Pour le refresh fonctionnel, j'ai du ajouter dans MyWorkbook/General les 2 fonctions suivantes:

Private Sub Workbook_Open()
    Application.Calculate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.Calculate
End Sub

En espérant que cela fera aussi gagner du temps a d'autres

Cdlt

Eric

En complément, les fonctions associées à MyWorkBook/General, parce qu'elles sont trop globales (application.calculate), interférent avec le copier/coller, qui n'est alors plus fonctionnel entre 2 onglets par exemple !!

Les fonctions ont donc été restreintes au bon périmètre de rafraichissement (onglet & plage):

' fonction de refreesh pour forcer calculs sur ouverture onglets, seulement sur mon Sheet/plage

Private Sub Workbook_Open()
    Sheets("Q1").Range("L11:Q1000").Calculate
End Sub

' fonction de refreesh pour forcer calculs sur ouverture onglets - complement - seulement sur mon Sheet/plage

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sheets("Q1").Range("L11:Q1000").Calculate
End Sub

Cela me permet un refresh de mes données et semble ne pas empêcher le copier/coller

Cdlt