XL 2019 Substituer fonction INDIRECT dans une formule

[KTM]

Bonsoir chers tous
A l'aide de la macro ci dessous j'insère en colonne P une formule matricielle contenant la fonction INDIRECT.

Sub Formule1()
Dim dl As Long
With Sheets("Grille_de_Disp")
 dl = .Range("A" & Rows.Count).End(xlUp).Row + 1
 .[Q11] = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("P2").FormulaArray = _
        "=MAX((R2C1:INDIRECT(""$A$""&R11C[1])=RC[-15])*(R2C2:INDIRECT(""$B$""&R11C[1])=RC[-14])*(R2C3:INDIRECT(""$C$""&R11C[1])=RC[-13])*(R2C11:INDIRECT(""$K$""&R11C[1])))"
 
      .Range("P2").AutoFill Destination:=.Range("P2:P" & .Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
  End With
End Sub

Mais il se trouve que ma macro est extrêmement lente et Il m'a été recommandé de réécrire ma formule en évitant la fonction INDIRECT.
j'ai essayé d'adapter un exemple avec la macro suivante mais je me mélange un peu les pédales .
Je vous prie de bien vouloir me corriger .Merci

Sub Formule2()
Dim dl As Long
With Sheets("Grille_de_Disp")
 dl = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("P2").FormulaArray = "=MAX("&"($A$2:$A$" & dl &")=A2)"&"*"&"($B$2:$B$" & dl &")=B2)"&"*"&"($C$2:$C$" & dl & ")=C2)"&"*"&"($K$2:$K$" & dl)"
.Range("P2").AutoFill Destination:=.Range("P2:P" & .Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
  End With
End Sub

 

[job75]

Bonsoir KTM,

Sur votre fil précédent votre commentaire c'est "super et bien !!".

Il aurait fallu nous dire si la suppression des INDIRECT diminuait la durée d'exécution et de combien !!

Si la durée d'exécution n'est pas réduite vous pouvez rester avec vos INDIRECT inutiles.

A+

 

[KTM]

Bonsoir KTM,

Sur votre fil précédent votre commentaire c'est "super et bien !!".

Il aurait fallu nous dire si la suppression des INDIRECT diminuait la durée d'exécution et de combien !!

Si la durée d'exécution n'est pas réduite vous pouvez rester avec vos INDIRECT inutiles.

A+

J'ai testé votre solution et la durée d'exécution est belle et bien réduite.
Dans ce poste-ci je voudrais adapter votre solution a un autre exemple avec formule matricielle mais je coince.

Dim dl As Long
With Sheets("Grille_de_Disp")
    dl = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("P2").FormulaArray = _
    "{=MAX(($A$2:$A$" & dl & ")=A2)*($B$2:$B$" & dl & ")=B2)*($C$2:$C$" & dl & ")=C2)*($K$2:$K$" & dl & ")}"
    .Range("P2").AutoFill Destination:=.Range("P2:P" & .Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault

End With

 

[job75]

J'ai testé votre solution et la durée d'exécution est belle et bien réduite.

Combien de secondes avec INDIRECT, combien de secondes sans ?

Indiquez aussi cela sur le fil concerné pour qu'il ait un sens.

 

[KTM]

Combien de secondes avec INDIRECT, combien de secondes sans ?

Indiquez aussi cela sur le fil concerné pour qu'il ait un sens.

Avec INDIRECT la macro met 20 secondes et 8 secondes sans INDIRECT.

 

[job75]

Merci, la réduction est intéressante, voici donc votre macro corrigée :

Sub Formule2()
Dim dl As Long
With Sheets("Grille_de_Disp")
    dl = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("P2").FormulaArray = "=MAX((A$2:A$" & dl & "=A2)*(B$2:B$" & dl & "=B2)*(C$2:C$" & dl & "=C2)*K$2:K$" & dl & ")"
    .Range("P2").AutoFill .Range("P2:P" & dl), xlFillValues
End With
End Sub

 

[job75]

Cela dit votre calcul de la variable dl n'est pas fameux, il vaut mieux écrire :

Sub Formule2()
Dim dl As Long
With Sheets("Grille_de_Disp")
    dl = .Range("A" & Rows.Count).End(xlUp).Row
    If dl = 1 Then Exit Sub
    .Range("P2").FormulaArray = "=MAX((A$2:A$" & dl & "=A2)*(B$2:B$" & dl & "=B2)*(C$2:C$" & dl & "=C2)*K$2:K$" & dl & ")"
    .Range("P2").AutoFill .Range("P2:P" & dl), xlFillValues
End With
End Sub

 

[KTM]

Merci, la réduction est intéressante, voici donc votre macro corrigée :

Sub Formule2()
Dim dl As Long
With Sheets("Grille_de_Disp")
    dl = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("P2").FormulaArray = "=MAX((A$2:A$" & dl & "=A2)*(B$2:B$" & dl & "=B2)*(C$2:C$" & dl & "=C2)*K$2:K$" & dl & ")"
    .Range("P2").AutoFill .Range("P2:P" & dl), xlFillValues
End With
End Sub

MERCI Job75
C'est super !!!
Prière vérifier ce qui suit. Toujours certains détails qui m'échappent. Merci

Sub Formule()
With Sheets("Grille_de_Disp")
Dim dl As Long
dl = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("Q4").FormulaR1C1 = _
    "=SUMPRODUCT((YEAR(B2:B" & dl & "))= TB!$B$5)*((B2:B" & dl & "))<= TB!$B$11))"
.Range("Q9").FormulaR1C1 = _
    "=SUMPRODUCT((YEAR(B2:B" & dl & "))= TB!$B$5)*(D2:D" & dl & ")= ""Oui"")*((B2:B" & dl & "))<= TB!$B$11))"
.Range("Q24").FormulaR1C1 = _
   "=COUNTIFS(B2:B" & dl & "),TB!B11,D2:D" & dl & "),""Oui"")"
.Range("Q25").FormulaR1C1 = _
    "=COUNTIFS(B2:B" & dl & "),TB!B11,D2:D" & dl & "),""T.In"")"
    .Range("Q26").FormulaR1C1 = _
"=SUMPRODUCT((YEAR(B2:B" & dl & "))= TB!$B$5)*(D2:D" & dl & ")= ""T.In"")*((B2:B" & dl & "))<= TB!$B$11))"
End With
End Sub

 

[job75]

Ecoutez on peut continuer longtemps comme ça, prenez votre temps pour créer les formules.

C'est simple : tant qu'elles ne sont pas correctes VBA les refuse.

Bonne nuit.

 

[KTM]

Ecoutez on peut continuer longtemps comme ça, prenez votre temps pour créer les formules.

C'est simple : tant qu'elles ne sont pas correctes VBA les refuse.

Bonne nuit.

Je vais m'y mettre.
Encore Merci.