Formule Matricielle en VBA

Le 05/07/2013 à 19:34

Bonjour,

Je souhaiterais faire marcher une fonction matricielle en VBA :

La voici lorsqu'elle est dans la cellule :

=NB(SI((DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!L"&MD!$E$24&":L"&MD!$F$24))*
(DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!E"&MD!$E$24&":E"&MD!$F$24))*
($A41>=INDIRECT("Cerner!V"&MD!$E$24&":V"&MD!$F$24))*
($A41<=INDIRECT("Cerner!W"&MD!$E$24&":W"&MD!$F$24));
INDIRECT("Cerner!K"&MD!$E$24&":K"&MD!$F$24)))

Et la voici en VBA :

Cells(40, I).FormulaArray = _
        "=COUNT(IF((DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C[-1])=INDIRECT(""Cerner!L""&MD!R24C5&"":L""&MD!R24C6))*" _
        & "(DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C[-1])=INDIRECT(""Cerner!E""&MD!R24C5&"":E""&MD!R24C6))*" _
        & "(RC1>=INDIRECT(""Cerner!V""&MD!R24C5&"":V""&MD!R24C6))*" _
        & "(RC1<=INDIRECT(""Cerner!W""&MD!R24C5&"":W""&MD!R24C6))," _
        & "INDIRECT(""Cerner!K""&MD!R24C5&"":K""&MD!R24C6)))"

Le fichier étant assez lourd et pensant que c'est plus une erreur de syntaxe (bien que j'utilise le générateur de macro) je tente de vous poser cette question déjà sans vous donner un fichier excel. Sinon je vous créerais une mini base de donnée. A noter qu'Excel ne met pas dans la cellule une fois le programme VBA lancé ni un message d'erreur ni une valeur nulle mais il plante et me renvoie sur le programme VBA en surlignant toute la formule en jaune.

Voilà si quelqu'un pouvait m'aider çà serait vraiment sympa, je suis bloqué depuis de nombreuses heures déjà.

Je vous remercie.

Le 06/07/2013 à 12:32

Salut Ginga999

Comme ça au passage,

ça va être terrible une formule matricielle de cette ampleur, lorsque tu vas avoir des tas de lignes, bonjour le temps de calcul

Le 06/07/2013 à 12:47

Bonjour,

En la mettant dans un nom calculé et avec :

Cells(40, I).Formula = ton_nom

ça ne passe pas ?

Ca serait plus simple.

(soit sur la bonne cellule lorsque tu crées ton nom)

eric

Le 08/07/2013 à 12:07
BrunoM45 a écrit :

Salut Ginga999

Comme ça au passage,

ça va être terrible une formule matricielle de cette ampleur, lorsque tu vas avoir des tas de lignes, bonjour le temps de calcul

En réalité ce n'est qu'un quart de la formule ^^

Mais déjà testé elle est assez rapide, les fonctions indirect permettent de périmetrer la plage de recherche au niveau des lignes ce qui accélère pas mal les choses. A ce niveau là il n'y a pas de soucis.

Le 08/07/2013 à 12:53
BrunoM45 a écrit :

Salut Ginga999

Bonjour,

En la mettant dans un nom calculé et avec :

Cells(40, I).Formula = ton_nom

ça ne passe pas ?

Ca serait plus simple.

(soit sur la bonne cellule lorsque tu crées ton nom)

eric

Le problème sera le même, je devrais écrire la formule, l'appliquer à une variable pour que celle-ci soit ensuite reprise. L'erreur se fera quand je renseignera la variable.

J'ai un code de 5 lignes, les 4 premiers étant des critères, la dernière celle qui sert à compter le nombre d'occurences :

Cells(40, I).FormulaArray = _
        "=COUNT(IF((DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C)=INDIRECT(""Cerner!L""&MD!R24C5&"":L""&MD!R24C6))*" _
        & "(DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C)=INDIRECT(""Cerner!E""&MD!R24C5&"":E""&MD!R24C6))*" _
        & "(RC1>=INDIRECT(""Cerner!V""&MD!R24C5&"":V""&MD!R24C6))*" _
        & "(RC1<=INDIRECT(""Cerner!W""&MD!R24C5&"":W""&MD!R24C6))," _
        & "INDIRECT(""Cerner!K""&MD!R24C5&"":K""&MD!R24C6)))"

Si je réduis la formule ne que retirant les lignes 3 et 4 , et que je garde donc les lignes 1,2 et 5cela fonctionne :

Cells(40, I).FormulaArray = _
        "=COUNT(IF((DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C)=INDIRECT(""Cerner!L""&MD!R24C5&"":L""&MD!R24C6))*" _
& "(DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C)=INDIRECT(""Cerner!E""&MD!R24C5&"":E""&MD!R24C6))," _
& "INDIRECT(""Cerner!K""&MD!R24C5&"":K""&MD!R24C6)))"

Bizarrement, si je rajoute la ligne 3 suivante çà ne marche plus :

& "(RC1>=INDIRECT(""Cerner!V""&MD!R24C5&"":V""&MD!R24C6))*" _

Cells(40, I).FormulaArray = _
        "=COUNT(IF((DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C)=INDIRECT(""Cerner!L""&MD!R24C5&"":L""&MD!R24C6))*" _
& "(DATE(2013,VLOOKUP(R2C2,MD!R2C2:R13C3,2,FALSE),R3C)=INDIRECT(""Cerner!E""&MD!R24C5&"":E""&MD!R24C6))," _
& "(RC1>=INDIRECT(""Cerner!V""&MD!R24C5&"":V""&MD!R24C6))*" _
& "INDIRECT(""Cerner!K""&MD!R24C5&"":K""&MD!R24C6)))"

Lorsque je ne met que la ligne 3 4 et 5 çà marche

Comme si les lignes 1 et 2 des critères ne pouvaient aller avec les lignes 3 et 4 de critères...

Je ne comprends pas...

Le 08/07/2013 à 13:25

Bon, je n'en peu plus là, j'en appel à un champion haha, je vous ai mis le fichier en PJ :

Il faut aller dans l'onglet TBORD

L'Utilisation du menu déroulant en B2 active la macro

Choisir un mois de Janvier à Avril (le reste né'tant pas renseigné)

Dire non à la MsgBox

La formule que je tente de rentrer étant

=NB(SI((DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)>INDIRECT("Cerner!E"&MD!$E$24&":E"&MD!$F$24))*
(DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)<INDIRECT("Cerner!L"&MD!$E$24&":L"&MD!$F$24));
INDIRECT("Cerner!K"&MD!$E$24&":K"&MD!$F$24)))+

NB(SI((DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!E"&MD!$E$24&":E"&MD!$F$24))*
(DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)<>INDIRECT("Cerner!L"&MD!$E$24&":L"&MD!$F$24))*
($A41>=INDIRECT("Cerner!V"&MD!$K$24&":V"&MD!$F$24));
INDIRECT("Cerner!K"&MD!$E$24&":K"&MD!$F$24)))+

NB(SI((DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!L"&MD!$E$24&":L"&MD!$F$24))*
(DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)<>INDIRECT("Cerner!E"&MD!$E$24&":E"&MD!$F$24))*
($A41<=INDIRECT("Cerner!W"&MD!$K$24&":W"&MD!$F$24));
INDIRECT("Cerner!K"&MD!$E$24&":K"&MD!$F$24)))+

NB(SI((DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!L"&MD!$E$24&":L"&MD!$F$24))*
(DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!E"&MD!$E$24&":E"&MD!$F$24))*
($A41>=INDIRECT("Cerner!V"&MD!$E$24&":V"&MD!$F$24))*
($A41<=INDIRECT("Cerner!W"&MD!$E$24&":W"&MD!$F$24));
INDIRECT("Cerner!K"&MD!$E$24&":K"&MD!$F$24)))

Mais si vous pouvez me faire passer ne serait que le dernier bout le reste passera càd, plus précisemment arriver à mettre les 4 premières lignes de critères du count matriciel :

NB(SI((DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!L"&MD!$E$24&":L"&MD!$F$24))*
(DATE(2013;RECHERCHEV($B$2;MD!$B$2:$C$13;2;FAUX);E$3)=INDIRECT("Cerner!E"&MD!$E$24&":E"&MD!$F$24))*
($A41>=INDIRECT("Cerner!V"&MD!$E$24&":V"&MD!$F$24))*
($A41<=INDIRECT("Cerner!W"&MD!$E$24&":W"&MD!$F$24));
INDIRECT("Cerner!K"&MD!$E$24&":K"&MD!$F$24)))

Bon courage aux aventuriers de ce site ^^

Je n'ai mis que le mois de Janvier sinon çà ne passait pas

23gestion-des-flux-v0-3.xlsm (254.14 Ko)
Le 08/07/2013 à 22:46

Bonsoir,

For I = 5 To DernLigne
Cells(40, I).Formula = ...

La syntaxe de Cells est Cells(ligne,colonne) et non l'inverse. Ca n'est pas ta question mais à mon avis il y a un pb ici.

Le problème sera le même, je devrais écrire la formule, l'appliquer à une variable pour que celle-ci soit ensuite reprise. L'erreur se fera quand je renseignera la variable.

Et si tu essayais avant de dire que ça ne marchera pas ?

Je n'ai pas l'impression que tu la modifies par une variable en vba. Ou alors il faudrait préciser où.

Ta formule rentre très bien dans un nom et :

Cells(40, I)= "=ton_nom" ne pose aucun problème, et un nom est toujours calculé en matriciel.

eric

edit: je viens de tester sur TBORD, pas de problème...

Le 09/07/2013 à 09:36

Oui concernant le

DernLigne = 35
For I = 5 To DernLigne

Je confirme bien avoir inversé, ligne et colonne, mais la formule fonctionne, en fait DernLigne représente la dernière colonne, la dénomination de la variable est fausse mais sa valeur reste correcte, je ne l'avais pas changée par ... fainéantise... Je vais quand même la corriger tu as raison, à chaque fois je me pose la question si c'est correcte.

Cells(40, I)= "=ton_nom" ne pose aucun problème, et un nom est toujours calculé en matriciel.

Tu peux me montrer comment tu fais ?

Appliquer une formule à une nom, j'imagine que c'est basique mais je ne l'ai jamais fais, je débute... c'est d'ailleurs pour çà aussi que je n'avais pas tenté de faire cette manipulation. Et si elle marche comme tu le dis, j'avoue que je ne comprendrais pas comment cela peut-être possible...

Je te remercie déjà pour tout ce temps que tu as pu passer.

Le 09/07/2013 à 10:15

Bonjour,

Ruban 'Formules / Noms définis / Gestionnaire de noms / Nouveaux...'

Saisir le nom et coller la formule dans 'Fait référence à"

Mais comme dans ton vrai code tu termines par un collage spécial valeur aucun intérêt à passer par des formules matricielles qui ralentissent tout. Autant tout calculer en mémoire et coller le résultat comme te l'avait suggéré bruno.

eric

40classeur3.zip (275.03 Ko)
Le 09/07/2013 à 14:06

C'est super intelligent.... je n'avais pas compris que l'affectation du nom à la formule se faisait hors VBA, ce qui permet donc de garder la première syntaxe qui elle marchait et le nom seul passe en VBA,... C'est super ! Vraiment !

J'ai une dernière question, pouvez-vous m'expliquer :

Mais comme dans ton vrai code tu termines par un collage spécial valeur aucun intérêt à passer par des formules matricielles qui ralentissent tout. Autant tout calculer en mémoire et coller le résultat comme te l'avait suggéré bruno.

En fait je ne comprends pas comment ont peut éviter les formules matricielles, j'avoue que çà m'intéresse, surtout que j'ai d'autres outils qui eux tournent pdt très longtemps,...

Eric, j'espère que tu m'excuses ainsi que Bruno lorsqu'au début j'ai "rejetté" vos réponses, c'était lié à une erreur de compréhension,...

Le 09/07/2013 à 18:22

Je ne vais pas te le faire car il faudrait décortiquer ta formule et là il n'y a que toi qui sait ce que tu cherches mais te donner le principe.

Le but est de charger les tables que tu as besoin dans des tableaux en mémoire et de travailler directement dessus.

Par exemple si tu as besoin de la table MD!G2:H8, nommer cette plage 'codeService' sur la feuille et :

dim codeServ as variant

codeServ = [codeService]

Pour une plage variable comme tes données de CERNER il faut faire comme ça (j'ai ajouté un semblant de traitement) :

Sub test()
    Dim derlig As Long, lig As Long, data As Variant
    Dim jour As Long
    Dim Acc_Med(1 To 5, 1 To 31)  ' 5 compteurs sur 31 jours
    ' récupérer datas
    With Sheets("CERNER")
        derlig = .Cells(Rows.Count, "A").End(xlUp).Row
        data = .[A2].Resize(derlig - 1, 27)
    End With

    ' analyser toutes les lignes de data
    For lig = 1 To UBound(data)
        ' faire les comptes dans des tableaux
        jour = Day(data(lig, 5))    ' récupère la date colonne 5. Simplifié...
        Select Case data
        Case 7108
            ' si autres tests ok (heure etc) incrémenter la bonne variable
            Acc_Med(2, jour) = Acc_Med(2, jour) + 1    'incrémenter tranche 2 : 9h
        Case Is = 7118
            ' si autres tests ok incrémenter  la bonne variable
        End Select
    Next lig

    ' coller le résultat
    Sheets("TBORD").[E40:AI44] = Acc_Med
End Sub

Bien sûr non fonctionnel et non testé, c'est pour te donner une idée de structure du code.

Ainsi tu calcules tout en bloc, chaque ligne n'est analysée qu'une fois et tu as tous tes compteurs d'un coup.

Si tu ne connais pas trop les tableaux : https://www.excel-pratique.com/fr/vba/tableaux_vba.php

eric

Le 10/07/2013 à 13:57

D'accord je vois le principe,...

J'ai encore des progrès à faire en VBA, j'irai voir votre cours sur les création de tables.

Je te remercie d'avoir passé autant de temps pour me répondre, mais je te promet que çà va m'être bien utile, je te remercie !

Bonne journée

Rechercher des sujets similaires à "formule matricielle vba"