FormulaArray - Impossible définir propriété FormulaArray de la classe Range

Bonjour,

Je rencontre un problème avec la FormulaArray. Pour remettre un peu de contexte j'utilise un tableau pour faire un pointage de véhicule qui passe dans ma zone avec leurs contenus. Pour ce faire j'utilise un fichier excel avec un tableau de fréquentation qui se présente sous la forme de une ligne = un passage et de plusieurs colonnes qui représentes des critères de passage.

L'idée que j'ai consiste à développer un deuxième tableau qui serait un tableau de résultat de recherche du premier. La recherche se fait via une cellule "recherche" Ainsi j'ai pour idée de faire une recherche dans mon tableau plutôt que d'avoir a systématiquement re-rentrer tous les critères pour chaque passage. Si la valeur rentré existe alors afficher tous les critères de la dernière ligne de mon premier tableau correspondant dans le deuxième pour pouvoir ensuite le rajouter à la fréquentation.

Lorsque je rentre une formule dans mon tableau de résultat de recherche et que j'applique manuellement CTRL + MAJ + ENTER, la formule passe bien en matricielle. L'idée est d'éviter d'avoir à faire cette manipulation et donc de coder en VBA une macro associer a un bouton reset qui mettrait directement les formules matricielles dans les cellules. Mais à chaque fois je rencontre toujours la même erreur : "Erreur d'éxécution '1004' impossible de définir la propriété FormulaArray de la classe Range.

Et depuis trois jours je suis bloqué dessus. J'ai vu sur d'autres forums qu'il fallait des références en A1 qu'il fallait que ce soit en anglais et qu'il fallait pas que ça dépasse les 256 caractères (chose que j'ai contourner par des Replace) mais je n'arrive toujours pas à faire ce que je souhaite faire d'où mon message de détresse

Voici donc le VBA pour ma formulaLocal (que je transforme manuellement en matricielle mais très long et fastidieux... Mais qui marche !) :

tbl12col2.FormulaLocal = "=SI(L(-6)C(2)="";"";SIERREUR(INDEX(Table_Freq;GRANDE.VALEUR(SI(Table_Freq[VIGNETTE MAE N°]=L(-6)C(2);LIGNE(Table_Freq[VIGNETTE MAE N°])-MIN(LIGNE(Table_Freq[VIGNETTE MAE N°]))+1);1);2);SIERREUR(INDEX(Table_Freq;GRANDE.VALEUR(SI(ESTNUM(CHERCHE(L(-6)C(2);Table_Freq[Concatenation]));LIGNE(Table_Freq[Concatenation])-MIN(LIGNE(Table_Freq[Concatenation]))+1);1);2);"?")))"

Voici le VBA de ma FormulaArray (que j'arrive pas à exécuter) :

Range("Tableau12[Heure]").Select
tbl12col2.FormulaArray =IF(D1974="""","""",1111=D1974,2222)+1),1),2),IFERROR(INDEX(Table_Freq,SMALL(IF(ISNUMBER(SEARCH(D1974,Table_Freq[Concatenation])),ROW(Table_Freq[Concatenation])-MIN(ROW(Table_Freq[Concatenation]))+1),1),2),""?"")))"

Selection.Replace What:="1111", Replacement:="IFERROR(INDEX(Table_Freq,SMALL(IF(Table_Freq[VIGNETTE MAE N°]", LookAt:=xlPart
Selection.Replace What:="2222", Replacement:="ROW(Table_Freq[VIGNETTE MAE N°])-MIN(ROW(Table_Freq[VIGNETTE MAE N°])", LookAt:=xlPart

Si une personne peut avoir une idée de quelle est le problème... Je suis preneur a 1000% !

Merci d'avance !

Bonjour,

L'argument de FormulaArray doit etre une string, or vous l'entrez "tel quel".

Cad, un mini exemple :

Range("A1:C5").FormulaArray = "=3"

Ajoutez des guillements autour, et n'oubliez pas le = au début.

Nouveau code, mais même erreur :(

Range("Tableau12[Heure]").FormulaArray = "=IF(D1959="""","""",IF(INDEX(Table_Freq,MATCH(D1959;Table_Freq[Concatenation],0),6),""?""))"
    Range("Tableau12[Heure]").Select
    Selection.Replace What:="1111", Replacement:="IFERROR(INDEX(Table_Freq,SMALL(IF(Table_Freq[VIGNETTE MAE N°]", LookAt:=xlPart
    Selection.Replace What:="2222", Replacement:="ROW(Table_Freq[VIGNETTE MAE N°])-MIN(ROW(Table_Freq[VIGNETTE MAE N°])", LookAt:=xlPart

Re,

Vraiment bizarre votre manipulation… Vous voulez changer le résultat affiché dans vos cellules ou la formule ?

De plus, vous essayez de changer des valeurs dans, justement, une arrayformula. C'est impossible, essayez de le faire manuellement vous n'y arriveriez pas. Le bloc Tableau12[Heure] n'est plus partiellement modifiable dès lors qu'il s'agit d'une arrayformula.

Solutions :

Remplacez les formules par leurs résultats :

Range(Tableau12[Heure]).Value2 = Range(Tableau12[Heure]).Value2

Ou bien ajoutez une nouvelle colonne.

Mais bon je dois dire que votre code est vraiment obscur, vous devriez joindre un fichier. De plus il est assez contre-intuitif d'entrez des formules via VBA. Autant faire votre logique en VBA et exporter les réultats dans Excel. Vous pouvez alternativement définir une fonction custom par exemple (UDF).

Re,

C'est mon premier gros projet en Excel, désolé si il n'y a pas forcément de grosse logique d'autant que j'avoue mettre pas mal aider de chat GPT.

Je vous partage un screen pour comprendre (avec changement de valeur par du fictif) :

La cellule Recherche Générale est ma cellule où je souhaite taper quelque chose pour savoir si c'est présent dans mon tableau du dessus "Table_Freq". Le deuxième tableau en bas (Tableau 12) est celui qui doit m'afficher la dernière ligne où il y a le résultat (si il y a résultat sinon c'est "?" comme dans l'exemple). Ce tableau est identique au premier excepté la colonne CONCATENATION qui me sert à faire la recherche.

La formule que je vous ai donné est celle du deuxième tableau colonne "Heure". L'idée est d'utiliser le deuxième tableau pour rentrer des valeurs si je ne les connais pas. Donc je perdrais les fonctions matricielles correspondant à ces valeurs puisque je devrais les saisirs manuellement. Pour retrouver la base de mes recherches j'ai besoin d'un bouton Reinitialisation présent à gauche de ma recherche générale. Relié à une macro il me permettra de reprendre mes fonctions matricielles qui me permet d'afficher un résultat si il existe. Dans la première capture j'ai bien un résultat donc pas de problème, je peux transférer les donnée de mon tableau12 vers mon Tableau de fréquentation. Dans le deuxième cas : je n'ai pas de résultat je dois donc entrer manuellement chaque colonne de mon tableau12 avant de les mettre dans le Table_Freq

Je peux apporter plus de précision voir founir un fichier simplifié si jamais quelqu'un a une idée ;)

Bonjour,

une technique infaillible et simple pour matricielles en VBA.

Va sur ta cellule avec la formule qui va bien.
Tu te mets en enregistrement de macro et tu fais une validation SIMPLE dessus. Arrêt de l'enregistreur.
Tu obtiens ta formule en R1C1 et en anglais.

L'astuce est de la mettre en 2 temps. En simple que tu revalides en matriciel ensuite.
En plus ça permet de passer la limite des 255 caractères de FormulaArray

' écrire formule
ta_cellule.FormulaR1C1 = "la formule en R1C1 anglais de l'enregistreur"
' valider en matriciel
ta_cellule.FormulaArray = ta_cellule.Formula

Met ta formule dans une variable texte si tu dois l'adapter avant de l'écrire
eric

bonjour grivetombe,eriiic,Saboh12617,

je pense qu'un simple "agregat" peut le faire au lieu de ces formules matricielles, mais je préfère un fichier simplifié pour le démonstrer.

Bonjour Eric, BsAlv

Eric, pour suivre tes conseils, j'ai procéder avec le code suivant :

    Dim tbl12col1 As Range
    Set tbl12col1 = firstRow.Cells(1,1)
    Dim formule_R1C1_date As String
    formule_R1C1_date = "=IF(R[-6]C[3]="""","""",IFERROR(INDEX(Table_Freq,LARGE(IF(Table_Freq[VIGNETTE MAE N°]=R[-6]C[3],ROW(Table_Freq[VIGNETTE MAE N°])-MIN(ROW(Table_Freq[VIGNETTE MAE N°]))+1),1),1),IFERROR(INDEX(Table_Freq,LARGE(IF(ISNUMBER(SEARCH(R[-6]C[3],Table_Freq[Concatenation])),ROW(Table_Freq[Concatenation])-MIN(ROW(Table_Freq[Concatenation]))+1),1),1),""?"")))"
    tbl12col1.FormulaArray = formule_R1C1_date

Je tombe malheureusement sur l'erreur 1004 impossible de définir la propriété FormulaArray de la classe Range à la dernière ligne.

BsAlv,

Un "agregat", ça ne me parle pas. En quoi ça consiste comparé à du matricielle ? Parce que j'ai cru comprendre que le matricielle était bien plus intéressant niveau performance et optimisation surtout pour des tableaux où il y a genre beaucoup de ligne (dans mon cas aujourd'hui je suis à environ 2000 lignes.

Je vous met une version simplifié de mon code avec le problème rencontré lorsque vous cliquez sur "réinitialisation". Merci pour vos réponses !

bonjour à tous, bonjour grivetombe,

ce que t'a proposé eriiic devrait être codé ainsi :

 Dim tbl12col1 As Range
    Set tbl12col1 = firstRow.Cells(1,1)
    Dim formule_R1C1_date As String
    formule_R1C1_date = "=IF(R[-6]C[3]="""","""",IFERROR(INDEX(Table_Freq,LARGE(IF(Table_Freq[VIGNETTE MAE N°]=R[-6]C[3],ROW(Table_Freq[VIGNETTE MAE N°])-MIN(ROW(Table_Freq[VIGNETTE MAE N°]))+1),1),1),IFERROR(INDEX(Table_Freq,LARGE(IF(ISNUMBER(SEARCH(R[-6]C[3],Table_Freq[Concatenation])),ROW(Table_Freq[Concatenation])-MIN(ROW(Table_Freq[Concatenation]))+1),1),1),""?"")))"
    tbl12col1.FormulaR1c1 = formule_R1C1_date
    tbl12col1.FormulaArray = tbl12col1.Formula

Bonjour à tous,

ce que t'a proposé eriiic devrait être codé ainsi

Tout à fait. Tu ne respecte même pas l'écriture en 2 temps préconisée.

Et que la formule soit bien issue d'une qui soit correcte sur la feuille, et récupérée par macro.
Ne compte pas sur ton talent pour écrire directement en vba et en anglais une formule comme ça.

Tu peux déposer un fichier avec ta formule dans une cellule, ça sera plus simple pour tout le monde de tester.
eric

bonjour h2so4, eriiic,grivetombe,

désolé mais agregat fonctionne à partir d'excel2013, donc votre 2010 est trop vieux.

Moi, j'essaie à eviter ces formules matricielles et je l'ai fait comme ceci,

* 2 plages nommées, votre "recherche" et "nombreCheck"

* une colonne "Check" supplémentaire dans le tableau "Table_Freq", qui donne le numéro de la ligne qui correspond au critère

* dans le tableau12, ce sont maintenant des formules simples, qui ne ralentisseront pas le fichier comme ces vraies formules matricielles.

Je me demande même si ce 2eme tableau est nécessaire parce que vous pouvez flitrer le premier maintenant.

Re,

pas sûr que la version du profil soit à jour vu qu'il utilise IFERROR qui n'existait pas sur 2010.
eric

Hello h2so4, Eriiic, BsAlv,

h2so4,

D'accord, désolé je ne comprenais pas la dernière ligne donc une ligne pour rentrer la formule, une autre pour la passer en R1C1 et une dernière pour la passer en FormulaArray. J'ai rectifié mais ça ne change pas l'erreur.

BsAlv

Une idée intéressante, effectivement en optimisation. Pour l'instant, je préfère me concentrer sur l'idée des deux tableaux : un pour faire la recherche/rentrer de nouvelles données et l'autre qui est un registre de passage. Mais c'est une autre approche qui évite les matricielles donc moins gourmand.

Eriiic,

Cette formule fonctionne pas en local (ça aussi c'est un mystère pour moi, systématiquement je tombe sur la valeur de "sierreur"). Mais elle fonctionne si on l'a passe en matricielle et c'est ça qui pose problème. Et même avec une macro faite pour l'occasion afin de récupérer les instructions, cela ne fonctionne pas. Mais en manuel (Ctrl + Maj + Enter) cela fonctionne. D'où mon problème.

Je rappel l'objectif principal : avoir lorsque je rentre une valeur dans "recherche générale" un "scan" du tableau au dessus, de cette valeur (d'abord la colonne Vignette MAE N°) puis ensuite la colonne CONCATENATION. Si il y a correspondance alors récupérer la valeur correspondante à la colonne de la plus grande ligne du tableau où est présente cette valeur sinon mettre "?" qui correspond à l'absence de correspondance de donnée.

Version d'Excel : Microsoft Office Famille et Étudiant 2010 Version 14.0.7153.5000.

Fichier mis à jour avec formule dans les cellules du deuxième tableau en local qu'il faut passer en matricielle pour avoir un résultat. Et le code de eric pour tenter de transformer la formule locale en matricielle de la première colonne du deuxième tableau en cliquant sur réinitialisation qui me marque l'erreur.

re,

ma version précédente fonctionnait sans macros !!! Il suffit d'avoir suffisant de lignes dans le 2eme tableau

autrement avec cette macro "grivetombe" et une adaptation à la macro "Worksheet_Change", cela fonctionne aussi. (sans formules matricielles)

Re,

Dans la version précédente, je n'ai pas besoin d'avoir l'intégralité des passages, j'ai juste besoin du dernier passage en date/heure qui s'affiche dans le tableau 2.

Merci beaucoup pour cette dernière version, effectivement ça se rapproche du résultat voulu. Je vais étudier un peu votre code ainsi que la macro "grivetombe' et tenter de comprendre du vrais VBA, moi qui pensait m'en sortir avec juste quelques formules qu'on met via des macros en fonction d'une situation et puis ça tourne .

Ca explique toujours pas pourquoi je n'arrive pas à passer en matricielle des formules mais ça m'ouvre les yeux sur une autre possibilité qui est moins gourmande en performance. Je suis toutefois preneur de comprendre le problème matriciel initiale si des personnes ont des pistes.

Merci encore !

re,

j'ai même des doutes si vous avez besoin des formules matricielles et surtout d'une macro. Dans ma version excel365, cela se voit plus si une formule est matricielle, donc ce sont des matricielles dans tableau12 ?

Re,

Merci pour l'itération concernant un seul et unique résultat lorsque l'on fait une recherche

Oui je confirme que ce sont bien des formules matricielles (exprimé par des accolades aux extrémité de la formule) dans le tableau12. Si je passe la formule de la dernière version que vous m'avez donné en formule non matricielle donc en validation standard, elle ne fonctionne plus. C'est comme les miennes, on tombe systématiquement sur la valeur SIERREUR, c'est à dire "-" dans le cas du dernier fichier. Le seule moyen qu'elle fonctionne c'est de les passer en matricielles.

Concernant les macros, dans mon utilisation, je m'en sers surtout pour placer des formules dans des cellules. Dans la philosophie d'utilisation du fichier, si jamais je ne connais pas la personne (donc si il est pas présent dans le tableau de fréquentation), j'écrase les valeurs "-" mis dans le tableau12 par des valeurs absolue (saisies à la main en dur dans les cases) ; voir l'exemple :

Du coup je perds les formules initiales, le tableau12 passe d'un tableau de résultat de recherche à un tableau de saisis de données. Une fois validé les données sur Tableau12 partent à la suite du Tableau de fréquentation via macro en appuyant sur le bouton ajouter Table_Freq.

Maintenant je souhaite repasser le tableau12 à l'utilisation initiale, à savoir le tableau de résultat de recherche en fonction de la valeur qu'il y a dans la recherche générale. J'avais pour idée de faire ça en macro via le bouton "réinitialisation".

Mais impossible de remettre les formules initiales en matricielle dans le tableau12 afin qu'il puisse reprendre sa fonction de "dernier résultat dans le tableau de fréquentation" puisque dans mes FormulaArray qui serait la fonction en macro permettant de remettre des formules dans des cellules définis sous forme matricielle, je rencontre l'erreur impossible de définir la propriété FormulaArray de la classe Range.

Et c'est précisément ça mon problème bloquant que je n'arrive pas à résoudre malgré le fait d'enregistrer une macro pour le faire, de passer les formulaarray en anglais, d'éviter que la fonction dépasse les x caractères... Mais si je met les formules en local et que je fait une validation matricielle à la main (Ctrl + MAJ + Enter en sélectionnant chacune des cellules du tableau12) tout fonctionne de nouveau ! Le but c'est d'éviter de devoir selectionner chacune des cellules manuellement et de faire un ctrl maj enter parce que c'est un temps perdu...

avec une plage nommée "Matricielle" pour contourner ce problème

Ah super ! Je vais appliquer ça et voir ce que ça donne en profondeur. Je vous tiens au courant, je suis bien pris en ce moment et je préfère bien prendre le temps de regarder tout ça. Merci encore pour vos éclaircissements !