XL 2019 Substituer fonction INDIRECT dans une formule

KTM · 14 Août 2021

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

VB:

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

Code:

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 · 14 Août 2021

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 · 14 Août 2021

job75 à dit:
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.

VB:

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 · 14 Août 2021

KTM à dit:
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 · 14 Août 2021

job75 à dit:
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 · 14 Août 2021

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

VB:

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 · 14 Août 2021

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

VB:

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 · 14 Août 2021

job75 à dit:

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

VB:

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

VB:

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 · 14 Août 2021

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 · 14 Août 2021

job75 à dit:
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.

XL 2019 Substituer fonction INDIRECT dans une formule

KTM

XLDnaute Impliqué

job75

XLDnaute Barbatruc

KTM

XLDnaute Impliqué

job75

XLDnaute Barbatruc

KTM

XLDnaute Impliqué

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

KTM

XLDnaute Impliqué

job75

XLDnaute Barbatruc

KTM

XLDnaute Impliqué

Discussions similaires