Discussion :

[Tchesko]

Bonjour,

Un petit casse-tête (pour moi en tout cas )
J'ai une feuille excel avec les données suivantes en A3:B7 :

5 A
3 B
6 C
3 D
4 E

Dans la même feuille, j'ai une formule matricielle utilisée pour faire du tri dynamique sur ces données. Appliquée au petit tableau ci-dessus, elle donne (tri par ordre décroissant qui gère les doublons éventuels) :

6 C
5 A
4 E
3 B
3 D

La formule (insérée par exemple en H9:I13) est la suivante :

Code :

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

=INDEX(INDEX($A$3:$B$7;;COLONNE($A$3:$B$7)-COLONNE(INDEX($A$3:$B$7;;1))+1);EQUIV(GRANDE.VALEUR($A$3:$A$7-LIGNE($A$3:$A$7)/10^10;LIGNE($A$3:$A$7)-LIGNE(INDEX($A$3:$A$7;1))+1);$A$3:$A$7-LIGNE($A$3:$A$7)/10^10;0))

Je précise que la formule est censée traiter des tableaux plus grands que l'exemple ci-dessus (ce qui explique que certaines parties de la formule soient superflues pour un tableau 5x2).
Cependant, la formule marche très bien si je l'entre directement dans la feuille excel (via Ctrl+Maj+Entrée).
Par contre, lorsque j'essaie de l'insérer par le code suivant, il y a un problème :

Code :

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

1
2
3
Sub Test()
Range("H9:I13").FormulaArray = "=INDEX(INDEX(R3C1:R7C2,,COLUMN(R3C1:R7C2)-COLUMN(INDEX(R3C1:R7C2,,1))+1),MATCH(LARGE(R3C1:R7C1-ROW(R3C1:R7C1)/10^10,ROW(R3C1:R7C1)-ROW(INDEX(R3C1:R7C1,1))+1),R3C1:R7C1-ROW(R3C1:R7C1)/10^10,0))"
End Sub

Il n'y a pas de message d'erreur, le code s'exécute correctement. En revanche, dans la feuille de calcul, j'obtiens le résultat suivant en H9:I13 :

#REF! #REF!
5 5
#REF! #REF!
A A
#REF! #REF!

Il suffit toutefois de sélectionner la plage H9:I13, appuyer sur F2 puis sur Ctrl+Maj+Entrée (sans toucher à la formule) et... miracle : le résultat correct apparaît !

Est-ce que quelqu'un pourrait m'expliquer pourquoi VBA+Excel ont ce comportement bizarre et trouver une façon d'obtenir directement le bon résultat à partir du code VBA sans avoir à "valider" derrière ?

[Sogestion]

Bonjour

Code :

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

1
2
3
4
5
6
 
Sub Trier()
 
Range("A3:A7").Sort [A3], xlDescending, MatchCase:=True
 
End Sub

_____________
Cordialement

[Tchesko]

Merci pour la réponse mais ce n'est pas ce que je souhaite faire.
Il ne s'agit pas de trier les données en statique mais d'insérer une formule de tri dynamique : si les valeurs de mon tableau de départ changent, le tableau trié doit changer aussi.
J'ai donc ma formule, mais comme elle est relativement longue et fastidieuse à taper, je voudrais automatiser son insertion en utilisant du code VBA. Et c'est là le problème...

[Sogestion]

Bonjour

... si les valeurs de mon tableau de départ changent, le tableau trié doit changer aussi.

Vous pouvez utiliser Private Sub object_Change( )
pour déclencher le tri des données

Exemples
Données reprises E3:E7
Plage a trier H10:H14

Toute modification dans la plage de données va déclancher la macro
et trier la plage H10:H14

Code testé qui fonctionne

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Private Sub Worksheet_Change(ByVal Target As Range) 
 
    Dim Lgn As Long
 
    If Target.Column = 5 Then
 
        Lgn = Target.Row
 
        Select Case Lgn
 
            Case 3 To 7
 
               Trier
 
        End Select
 
    End If
 
End Sub

_______________

Cordialement

[Tchesko]

OK, merci pour cette réponse qui a le mérite de ne pas présenter d'effets secondaires indésirables.
Il reste toutefois la question qui défie ma curiosité : pourquoi VBA+Excel ont ce comportement bizarre que j'ai décrit dans mon 1er post ?