Discussion :

[Florent18]

Bonjour à toutes et tous,

Je me présente, Florent

J'ai la formule suivante qui doit être matricielle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SI(E16="";"";SI(ESTNA(INDEX(INDIRECT("'"&"Absences_"&Récap!$Q$1&"'"&"!U2:U300");EQUIV(N16&$A$2;INDIRECT("'"&"Absences_"&Récap!$Q$1&"'"&"!A2:A300")&INDIRECT("'"&"Absences_"&Récap!$Q$1&"'"&"!N2:N300");0)));"";INDEX(INDIRECT("'"&"Absences_"&Récap!$Q$1&"'"&"!U2:U300");EQUIV(N16&$A$2;INDIRECT("'"&"Absences_"&Récap!$Q$1&"'"&"!A2:A300")&INDIRECT("'"&"Absences_"&Récap!$Q$1&"'"&"!N2:N300");0))))}

N'étant pas un expert en macro, je la transforme via l'enregistreur de macro et il me donne ceci (le "Selection" est remplacé par Range("O4"):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Selection.FormulaArray = _
        "=IF(RC[-10]="""","""",IF(ISNA(INDEX(INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!U2:U300""),MATCH(RC[-1]&R2C1,INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!A2:A300"")&INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!N2:N300""),0))),"""",INDEX(INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!U2:U300""),MATCH(RC[-1]&R2C1,INDIRECT(""'""&""Absences_""&Récap!R" & _
        """&""!A2:A300"")&INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!N2:N300""),0))))"

Il me donne Erreur d'exécution '1004' :

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

En rouge, j'ai vu qu'Excel m'a fait un truc étrange puisque je suis censé avoir, je suppose Récap!R1C17 au lieu de Récap!R. J'ai bien compris que l'espace + "_" était pour passer à la ligne suivante mais lorsque j'essaie de compléter et de passer à la ligne suivante, j'ai une erreur de compilation et pourtant, il me semble faire attention à ne pas scinder des "".
Pourriez-vous, svp, m'aider à voir plus clair?

Merci d'avance,

Bien à vous,

Florent.

[joe.levrai]

Bonjour,

256 caractères est la limite de FormulaArray

pour la dépasser, on peut ruser

de mon côté, j'injecte via le FormulaArray le "squelette" de la formule avec des briques de remplacement
ensuite, je fais mes remplacement

exemple : j'ai une formule d'environ 500 caractères, où j'ai découpé 6 briques de remplacement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
With Feuille_Masque.Cells(1, 15)
    .FormulaArray = "=AVERAGE(IF((1111)*(2222)*(3333)*(4444)*(5555),6666))"
    .Replace 1111, Raccourci_BDD & "[DATE]=" & CDbl(CDate(LesJours(i))), lookat:=xlPart
    .Replace 2222, Raccourci_BDD & "[MASTER_DOMAINE]=""" & LePartenaire(2) & """", lookat:=xlPart
    .Replace 3333, Raccourci_BDD & "[FLUX]=""STOCK""", lookat:=xlPart
    .Replace 4444, Raccourci_BDD & "[ITEM]<>""TRANS RC""", lookat:=xlPart
    .Replace 5555, Raccourci_BDD & "[ITEM]<>""FAX""", lookat:=xlPart
    .Replace 6666, Raccourci_BDD & "[PLUS_ANCIEN]", lookat:=xlPart
End With

ainsi, après les remplacements, je retrouve ma formule matricielle intégrale

[Florent18]

Bonjour joe.levrai,

Merci pour ta réponse. J'ai seulement quelques connaissances très basiques en macro et je ne comprends pas la construction de la macro.

Comment faites-vous pour mettre les lignes de code bien structuré sur le forum (afin que j'essaie de faire pareil pour bien structurer)?

Merci d'avance,

Florent.

[unparia]

Bonjour

Comment faites-vous pour mettre les lignes de code bien structuré sur le forum (afin que j'essaie de faire pareil pour bien structurer)?

https://club.developpez.com/regles/
(chapitre III e)

[Florent18]

Merci Unparia pour le lien.

Le code suivant me plante à la ligne FormulaArray = _
"IF(...), avec le même code erreur.

Je ne suis pas sûr que mes lignes suivantes soient correctes. Mais j'avoue ne pas avoir compris donc j'ai essayé d'adapter. Voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
With ActiveSheet.Range("O4")
        .FormulaArray = _
        "=IF(RC[-10]="""","""",IF(ISNA(INDEX(INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!U2:U300""),MATCH(RC[-1]&R2C1,INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!A2:A300"")*(1111)*(2222)"
        .Replace 1111, "&INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!N2:N300""),0))),"""",INDEX(INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!U2:U300""),MATCH(RC[-1]&R2C1,INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!A2:A300""), lookat:=xlPart"
        .Replace 2222, "&INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!N2:N300""),0))))", lookat:=xlPart
    End With

J'ai essayé avec ceci également :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
    With ActiveSheet.Range("O4")
        .FormulaArray = _
        "=IF(RC[-10]="""","""",IF((1111)*(2222)*(3333))"
        .Replace 1111, "ISNA(INDEX(INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!U2:U300""),MATCH(RC[-1]&R2C1,INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!A2:A300"")", lookat:=xlPart
        .Replace 2222, "&INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!N2:N300""),0))),"""",INDEX(INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!U2:U300""),MATCH(RC[-1]&R2C1,INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!A2:A300""), lookat:=xlPart"
        .Replace 3333, "&INDIRECT(""'""&""Absences_""&Récap!R1C17&""'""&""!N2:N300""),0)))", lookat:=xlPart

Merci d'avance pour votre patience!

[Florent18]

Re bonjour à tous,

Je vous remercie d'avoir pris le temps, j'ai modifié en ajoutant des colonnes et en concatenant pour éviter la formule matricielle.

Merci

Je reviendrai sans doute

[LePrinceCeleste]

Bonjour,

256 caractères est la limite de FormulaArray

pour la dépasser, on peut ruser

de mon côté, j'injecte via le FormulaArray le "squelette" de la formule avec des briques de remplacement
ensuite, je fais mes remplacement

exemple : j'ai une formule d'environ 500 caractères, où j'ai découpé 6 briques de remplacement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
With Feuille_Masque.Cells(1, 15)
    .FormulaArray = "=AVERAGE(IF((1111)*(2222)*(3333)*(4444)*(5555),6666))"
    .Replace 1111, Raccourci_BDD & "[DATE]=" & CDbl(CDate(LesJours(i))), lookat:=xlPart
    .Replace 2222, Raccourci_BDD & "[MASTER_DOMAINE]=""" & LePartenaire(2) & """", lookat:=xlPart
    .Replace 3333, Raccourci_BDD & "[FLUX]=""STOCK""", lookat:=xlPart
    .Replace 4444, Raccourci_BDD & "[ITEM]<>""TRANS RC""", lookat:=xlPart
    .Replace 5555, Raccourci_BDD & "[ITEM]<>""FAX""", lookat:=xlPart
    .Replace 6666, Raccourci_BDD & "[PLUS_ANCIEN]", lookat:=xlPart
End With

ainsi, après les remplacements, je retrouve ma formule matricielle intégrale

Bonjour, J'ai essayé l'astuce ci-dessus.

Ci suivant mon code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test()
Dim ligneMois
    ligneMois = "(IFERROR(MONTH('Rest. (Total)'!R1C1:R999C1),0)=MONTH(R1C9))*(IFERROR(YEAR('Rest. (Total)'!R1C1:R999C1),0)=YEAR(R1C9))*ROW('Rest. (Total)'!R1:R999)"

    With Range("J1")
        .FormulaArray = _
            "=IFERROR(IF(SUMPRODUCT(1111)=0,0," & Chr(10) & _
            "INDEX('Rest. (Total)'!R1C1:R999C5,SUMPRODUCT(1111)" & _
            ",COLUMN('Rest. (Total)'!R1C1))),0)" & _
            ""
        .Replace What:="1111", Replacement:=ligneMois, LookAt:=xlPart
    End With
End Sub

Tout se déroule bien jusqu'à la fin sans message d'erreur, mais le remplacement n'a pas été effectué tel que demandé.

Pouvez vous m'aider svp?

D'avance un grand MERCI à tous qui prendront un temps pour mon problème!

[ARTURO83]

Bonjour,

Pourquoi vous avez greffé votre message sur une demande vieille de 4 ans au lieu de créer une nouvelle discussion ? Comme le sujet est déjà passé en "résolu", peu de gens vont l'ouvrir.
Dans votre formule que signifie sommeprod(1111) ?

dans la première formule, 1111 est numérique et dans la formule suivante vous recherchez le texte "1111". supprimez les guillemets.

Cdlt

[LePrinceCeleste]

Bonjour,

Je suis désolé, j'aurais en effet dû créer un nouveau sujet.

1. Dans SOMMEPROD(1111):
   
   • 1111 remplace (SIERREUR(MOIS('Rest. (Total)'!$A$1:$A$999);0)=MOIS($I$1))*(SIERREUR(ANNEE('Rest. (Total)'!$A$1:$A$999);0)=ANNEE($I$1))*LIGNE('Rest. (Total)'!$1:$999)
     
     
   • pour donner: SOMMEPROD((SIERREUR(MOIS('Rest. (Total)'!$A$1:$A$999);0)=MOIS($I$1))*(SIERREUR(ANNEE('Rest. (Total)'!$A$1:$A$999);0)=ANNEE($I$1))*LIGNE('Rest. (Total)'!$1:$999))
   
   
2. En mettant les guillemets ou non, le résultat est malheureusement le même.

Merci d'avoir pris le temps de me répondre.

[ARTURO83]

Pas tout compris ce que c'est censé faire, une copie d'écran de quelques lignes de votre fichier qui couvre les plages concernées serait souhaitable.
Ainsi que la formule Excel (pas traduite en VBA) que vous appliquez en J1.

[LePrinceCeleste]

Ci-dessous deux images. Sur la première on voit la feuille citée dans la formule et où se trouve la formule.
Sur la deuxième on voit la feuille citée dans la formule également.

SOMMEPROD permet de trouver ou non dans la colonne "A" de la feuille "Rest. (Total)", la ligne du mois qui apparaît dans la celulle "I1" de la feuille "Rest. (Liste)".
L'idée en gros est de vérifier que le tableau contient bien les données du mois précédent avant de traiter le mois en cours.

=> Voici la formule matricielle complète dans la celulle "J1" de la feuille "Rest. (Liste)":

=SIERREUR(SI(SOMMEPROD((SIERREUR(MOIS('Rest. (Total)'!$A$1:$A$999);0)=MOIS($I$1))*(SIERREUR(ANNEE('Rest. (Total)'!$A$1:$A$999);0)=ANNEE($I$1))*LIGNE('Rest. (Total)'!$1:$999))=0;0;INDEX('Rest. (Total)'!$A$1:$E$999;SOMMEPROD((SIERREUR(MOIS('Rest. (Total)'!$A$1:$A$999);0)=MOIS($I$1))*(SIERREUR(ANNEE('Rest. (Total)'!$A$1:$A$999);0)=ANNEE($I$1))*LIGNE('Rest. (Total)'!$1:$999));COLONNE('Rest. (Total)'!$A$1)));0)

[ARTURO83]

Je ne comprends pas toutes les subtilités de votre formule, si vous voulez vérifier la présence du mois indiqué en I1 de "Rest. 'Liste", pourquoi ne pas utiliser EQUIV qui vous renverra le N° de la ligne s'il le trouve:

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV($I$1;'Rest. (Total)'!$A$1:$A$999;0)

[LePrinceCeleste]

Merci encore ARTURO83 pour tes réponses.

Ma formule matricielle fonctionne très bien et me renvoie les bonnes réponses quand elle est insérée manuellement.

Dans ce cas, par contre, je souhaite qu'une Macro l'insère dans la celulle "J1" à un moment donné.
Toutefois, comme c'est une formule matricielle et que le nombre de caractères dépasse les 256 autorisés, alors j'ai décidé de contourner cette limitation en utilisant l'astuce proposée ci-dessus par joe.levrai le 08/02/2017 à 18h35.

J'ai donc d'abord raccourci la formule en remplaçant un bout par le chiffre 1111, puis en remplaçant ce chiffre à l'aide de la fonction "replace" par le bout manquant.
>>> Et c'est ÇA que je n'arrive pas à faire.
>>> C'est ÇA mon problème.
La macro s'exécute bien, sans donner d'erreurs, mais aucun remplacement n'a été fait.

[ARTURO83]

Normalement votre formule devrait s'écrire comme ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
   Range("J1").FormulaArray = _
            "=IFERROR(IF(SUMPRODUCT(" & ligneMois & ")=0,0," & Chr(10) & _
            "INDEX('Rest. (Total)'!R1C1:R999C5,SUMPRODUCT(" & ligneMois & ")" & _
            ",COLUMN('Rest. (Total)'!R1C1))),0)"""

Mais à mon avis, la variable "LigneMois" constituée d'un extrait de formule est fausse ou incomplète, essayez de l'écrire en excel dans une cellule et lorsque vous serez sûr de son fonctionnement alors vous pourrez l'intégrer à la formule finale via la variable comme je l'ai montré.

[LePrinceCeleste]

J'ai en effet déjà essayé d'écrire la formule ainsi.

Le souci est qu'en mettant ligneMois dans FormulaArray, cela revient à écrire le bout de formule qu'il représente en entier et dépasser de ce fait les 256 caractères autorisés. VBA renvoit immédiatement un message d'erreur 1004 "Impossible de définir la propriété FormulaArray de la classe Range."

Je suis également de votre avis que la variable "LigneMois" doit être incorrecte:

- Vous proposez de l'écrire dans une cellule, et je l'ai fait. Je vous prie de voir cette courte VIDÉO

- J'ai cherché longtemps, et testé plusieurs cas de figure avant de me tourner vers vous. Généralement j'arrive à trouver une solution, mais là, je sèche complètement.

[LePrinceCeleste]

PROBLÈME RÉSOLU!!!

Mon code VBA lineMonth était correct à UN défaut près (comme d'habitude) :

• Les références des cellules étaient en R1C1 !!!
• Alors qu'elles devaient être en A1 !!!

Ci-dessous l'image du code correct :



En rouge, le code tel qu'il était avant et en noir, le code tel qu'il doit être.

Conclusion :
>> L'une des règles à respecter pour que la fonction "replace" fonctionne: Le texte à remplacer doit être en ANGLAIS et en A1.

ENCORE UNE FOIS, MERCI POUR LE TEMPS PRIS POUR M'AIDER À RÉSOUDRE MON PROBLÈME.
Je vous souhaite tout ce qu'il y a de meilleur pour vous et tous ceux qui vous sont chers !