Formule matricielle trop longue

[NICOALBERT]

Bonsoir le forum ,

Je vient vers vous car je suis en cours de réaliser un fichier avec des formule matricielle le tout par macro (recherche et activation) .

Ceux qui font moins de 255 caractères marche très bien mais pour ceux supérieur à 255 caractères j'ai un message d'erreur "Erreur d'exécution '1004': Impossible de définir la propriété FormulaArray de la classe Range" .

Voici la macro que j'utilise :

Sub MacroPerformanceFaceAFace()
'
' MacroPerformanceFaceAFace Macro
'
Sheets("Formule").Select ' Performance Globale EQ2

Range("B23").Select

ActiveCell.FormulaR1C1 = _ "=INDEX(Base!R1C7:R500000C7,LARGE(IF(Base!R2C1:R500000C1=Base!R3C1:R500001C1,IF(Base!R2C5:R500000C5&Base!R3C5:R500001C5=R1C4&R11C4,ROW(Base!R2C1:R500000C1),IF(Base!R2C5:R500000C5&Base!R3C5:R500001C5=R11C4&R1C4,ROW(Base!R3C5:R500001C5)))),COLUMNS(C1:C[-1])))"

Range("B23").Select

Selection.FormulaArray = _ "=INDEX(Base!R1C7:R500000C7,LARGE(IF(Base!R2C1:R500000C1=Base!R3C1:R500001C1,IF(Base!R2C5:R500000C5&Base!R3C5:R500001C5=R1C4&R11C4,ROW(Base!R2C1:R500000C1),IF(Base!R2C5:R500000C5&Base!R3C5:R500001C5=R11C4&R1C4,ROW(Base!R3C5:R500001C5)))),COLUMNS(C1:C[-1])))"
Selection.Copy ' Copie les valeurs
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False 'colle valeur sans formule

End Sub

Est ce que quelqu'un a une idée pour ne pas utiliser "FormulaArray" ou différemment ?

Cdlt Nicoalbert .

 

[eriiic]

Bonjour,

pas besoin de .Select, ça prend beaucoup de temps pour rien.

Tu la mets en R1C1 et tu la revalides en matricielle :

    With Range("B23")
        .FormulaR1C1 = "=blablabla"
        .FormulaArray = .Formula
        .Value = .Value    'garde la valeur
    End With

Par curiosité tu peux tenter avec :

Range("B23").value = Evaluate("ta_formule")

voir si la longueur gêne aussi.
eric

 

[NICOALBERT]

Bonsoir eric , le forum ,

Merci pour le temps que tu prend pour m'aider .

Lorsque j'essai cette macro j'ai ce message d'erreur "Erreur d'exécution '1004': Erreur définie par l'application ou par l'objet" !!!

Sub essai()
With Range("B23")
.FormulaR1C1 =
"=INDEX(Base!$G$1:$G$500000;GRANDE.VALEUR(SI(Base!$A$2:$A$500000=Base!$A$3:$A$500001;SI(Base!$E$2:$E$500000&Base!$E$3:$E$500001=$D$1&$D$11;LIGNE(Base!$A$2:$A$500000);SI(Base!$E$2:$E$500000&Base!$E$3:$E$500001=$D$11&$D$1;LIGNE(Base!$E$3:$E$500001))));COLONNES($A:A)))"
.FormulaArray = .Formula
.Value = .Value 'garde la valeur
End With

End Sub


Cdlt.

 

[eriiic]

Ah ben forcément..
.FormulaR1C1 : il faut garder la notation R1C1, tu as mis en notation A1

Sinon tu peux aussi faire :

.FormulaLocal = "=INDEX(Base!$G$1:$G$500000;GRANDE.VALEUR(SI(Base!$A$2:$A$500000=Base!$A$3:$A$500001;SI(Base!$E$2:$E$500000&Base!$E$3:$E$500001=$D$1&$D$11;LIGNE(Base!$A$2:$A$500000);SI(Base!$E$2:$E$500000&Base!$E$3:$E$500001=$D$11&$D$1;LIGNE(Base!$E$3:$E$500001))));COLONNES($A:A)))"
.FormulaArray = .Formula

Par contre je viens de voir que .FormulaArray = .Formula bloque aussi...
Je crains que tu ne sois obligé d'en sortir un morceau pour la raccourcir.
Ou bien nomme tes plages. En plus de raccourcir ça rendra ta formule plus lisible.
Et tu as vraiment besoin de 50000 lignes ? Mets des noms dynamiques, ça ira plus vite.

Et ça a donné quoi le test d'Evaluate() ?
eric

 

[job75]

Bonsoir NICOALBERT, eriiiic,

Pour raccourcir la formule il suffit de nommer les plages :

Sub MacroPerformanceFaceAFace()
With Sheets("Base")
    .[G1:G500001].Name = "ColG" 'nomme la plage
    .[A2:A500000].Name = "ColA" 'nomme la plage
    .[A3:A500001].Name = "ColAD" 'nomme la plage décalée
    .[E2:E500000].Name = "ColE" 'nomme la plage
    .[E3:E500001].Name = "ColED" 'nomme la plage décalée
End With
With Sheets("Formule").[B23]
    .FormulaArray = "=INDEX(ColG,LARGE(IF(ColA=ColAD,IF(ColE&ColED=$D$1&$D$11,ROW(ColA),IF(ColE&ColED=$D$11&$D$1,ROW(ColAD)))),COLUMNS($A:A)))"
    .Value = .Value 'supprime la formule
End With
End Sub

Bonne nuit.

 

[eriiic]

Salut job,
oui, j'avais édité mon post en ce sens

Par contre je suis étonné que tu passes en A1. C'est même indiqué dans l'aide :

Si vous utilisez cette propriété pour entrer une formule sous forme matricielle, vous devez utiliser le style de référence L1C1, et non le style de référence A1 (consultez le second exemple).

au passage on y lit aussi :

La propriété FormulaArray a également une limite de 255 caractères

 

[NICOALBERT]

Eriiiic , Job75 ,

ça fonctionne parfaitement , je vous remercie tous les deux pour l'aide que vous m'avez apportée , et le temps pris pour cela .

Cdlt Nicoalbert .

 

[job75]

Bonjour NICOALBERT, eriiiic, le forum,

En fait la formule provient de ce fil récent :

https://www.excel-downloads.com/threads/affichage-info-selon-condition.20021062/#post-20195821

Cette macro est adaptée à la dimension du tableau en feuille "Base" :

Sub MacroPerformanceFaceAFace()
Application.ScreenUpdating = False
With Sheets("Base").[A1].CurrentRegion
    .Columns(7).Name = "ColG" 'nomme la plage
    .Columns(1).Name = "ColA" 'nomme la plage
    .Columns(1).Offset(1).Name = "ColAD" 'nomme la plage décalée
    .Columns(5).Name = "ColE" 'nomme la plage
    .Columns(5).Offset(1).Name = "ColED" 'nomme la plage décalée
End With
With Sheets("Formule")
    .[B23].FormulaArray = "=INDEX(ColG,LARGE(IF(ColA=ColAD,IF(ColE&ColED=$D$1&$D$11,ROW(ColA),IF(ColE&ColED=$D$11&$D$1,ROW(ColAD)))),COLUMNS($A:A)))"
    .[B23].Copy .[C23:K23]
    .[B23:K23] = .[B23:K23].Value 'supprime les formules
    .[B25].FormulaArray = "=INDEX(ColG,LARGE(IF(ColA=ColAD,IF(ColE&ColED=$D$11&$D$1,ROW(ColA),IF(ColE&ColED=$D$1&$D$11,ROW(ColAD)))),COLUMNS($A:A)))"
    .[B25].Copy .[C25:K25]
    .[B25:K25] = .[B25:K25].Value 'supprime les formules
End With
End Sub

En B23:K23 et B25:K25 se trouvent les résultats des rencontres en face à face.

Fichier .xlsm joint.

Bonne journée.

 

[NICOALBERT]

Bonjour Job75 , le forum ,

Désolé pour cette réponse tardive , ta macro me fait gagner un temps de fou.

Moi la mienne s’arrêter à chaque cellule et recommencer le calcul à chaque fois .

Encore un très grand merci.

cdlt Nicoalbert

 

[NICOALBERT]

Re-bonjour Job75 ,

Ta formule fonctionne très bien , est ce qu'elle est modifiable pour rechercher les infos d'un joueur précis en fonction de l'équipe qu'il va rencontrer et de l'équipe à venir .
Exemple : le Match du 06/10/2012 = DEN contre LAC , je cherche à connaitre les perf d'un joeur (ex: Ty Lawson) .
Pour connaitre combien de temps il joue contre Los Angeles Clippers sachant que le prochain match l'équipe affronte San Antonio Spurs .

Cdlt Nicoalbert .