VBA - Remplacer une formule matricielle par un code

[R@chid]

Bonjour @ tous,
sur le fichier ci-joint, je cherche à remplacer une formule matricielle sur l'onglet "Etat" par un code VBA.

Merci

 

[Dranreb]

Re : VBA - Remplacer une formule matricielle par un code

Bonsoir R@chid.

Chez moi c'est bizarre, mais c'est comme dans Excel 2002, le changement de choix dans les zones combinées de formulaire (et donc le changement de N1 ou N2) ne déclenchent pas l'évènement Worksheet_Change.
Il faudrait donc je pense des ComboBox ActiveX à la place…
Après c'est pas compliqué on charge… oh et puis je le fais ce sera plus rapide.
Voila :

Sub TriProfDate()
Dim Prof, Dat As Date, A&, M&, Te(), Le&, Ts(), Ls&, C
Prof = [B1].Value
Dat = [B2].Value: M = Month(Dat): A = Year(Dat)
Te = Feuil1.ListObjects(1).DataBodyRange.Value
ReDim Ts(1 To UBound(Te, 1), 1 To UBound(Te, 2) - 1)
For Le = 1 To UBound(Te, 1)
   Dat = Te(Le, 1)
   If Month(Dat) = M And Year(Dat) = A And Te(Le, 2) = Prof Then
      Ls = Ls + 1
      Ts(Ls, 1) = Te(Le, 1)
      For C = 3 To UBound(Te, 2): Ts(Ls, C - 1) = Te(Le, C): Next C
      End If
   Next Le
Me.[A8:F100].ClearContents
Me.[A8].Resize(Ls, UBound(Ts, 2)).Value = Ts
End Sub

Dans le module de la feuille, à appeler donc quand une ComboBox ActiveX change.

 

[Dranreb]

Re : VBA - Remplacer une formule matricielle par un code

*** Doublon de poste, à supprimer.

 

[R@chid]

Re : VBA - Remplacer une formule matricielle par un code

Bonjour,
merci Dranreb pour le code.
mais je n'arrive pas à le faire fonctionner puisque j'ai jamais utilisé de ComboBox ActiveX.
en essayant de déclencher la macro par un bouton, il me renvoie une erreur ==> Erreur de compilation : utilisation incorrecte du mot clé Me.

Merci

 

[Dranreb]

Re : VBA - Remplacer une formule matricielle par un code

Bonjour.
Tu dois l'avoir mis dans un module standard au lieu du module de la feuille Etat comme je disais. Il suffit alors de remplacer Me par le nom d'objet Worksheet de la feuille Etat (celui rendu par sa propriété CodeName).
Attention aussi aux [B1]: mettre aussi Feuil2.[B2] si c'est bien Feuil2, je ne sais plus, je n'ai pas gardé le classeur

 

[R@chid]

Re : VBA - Remplacer une formule matricielle par un code

Re,
c'est bon ça marche comme ça, avec le champ MaSomme = N3 où j'ai fait la somme de N1:N2.

Private Sub Worksheet_Calculate()
Dim Prof, Dat As Date, A&, M&, Te(), Le&, Ts(), Ls&, C
Prof = [B1].Value
Dat = [B2].Value: M = Month(Dat): A = Year(Dat)
Te = Feuil1.ListObjects(1).DataBodyRange.Value
Static Somme
If Range("MaSomme") <> Somme Then
Somme = Range("MaSomme")
ReDim Ts(1 To UBound(Te, 1), 1 To UBound(Te, 2) - 1)
For Le = 1 To UBound(Te, 1)
   Dat = Te(Le, 1)
   If Month(Dat) = M And Year(Dat) = A And Te(Le, 2) = Prof Then
      Ls = Ls + 1
      Ts(Ls, 1) = Te(Le, 1)
      For C = 3 To UBound(Te, 2): Ts(Ls, C - 1) = Te(Le, C): Next C
      End If
   Next Le
Me.[A8:F100].ClearContents
Me.[A8].Resize(Ls, UBound(Ts, 2)).Value = Ts
End If
End Sub

MaPomme

@ + +

 

[R@chid]

Re : VBA - Remplacer une formule matricielle par un code

Re,
je l'ai mis dans un module standard mais j'ai gardé le Me c'est ça..
maintenant je l'ai adapté comme sur le post #6, mais il bug quand le prof n'avait pas de séance pendant un mois, et voilà le rôle de On Error Resume Next.

Merci


@ + +

 

[Dranreb]

Re : VBA - Remplacer une formule matricielle par un code

Tu doit pouvoir le mettre tout au début pour éviter le chargement inutile de Te.
Et je ferais sans doute plutôt :

If Me.[Somme] = Somme Then Exit Sub

Et il vaudrait mieux que Somme soit déclaré en tête du module de feuille pour qu'il existe un moyen d'agir autrement dessus qu'en changeant les choix. À mon avis mieux le laisser sous forme de procédure TriProfDate :

Option Explicit
Dim Somme

Private Sub Worksheet_Activate()
TriProfDate
Somme = Me.[Somme]
End Sub

Private Sub Worksheet_Calculate()
If Me.[Somme] = Somme Then Exit Sub
TriProfDate
Somme = Me.[Somme]
End Sub

 

[R@chid]

Re : VBA - Remplacer une formule matricielle par un code

Ok,
pourquoi pas je testerai et je te tiendrai au courant.

Merci

 

[Dranreb]

Re : VBA - Remplacer une formule matricielle par un code

Ah et pour le dernier problème met If Ls = 0 then Exit Sub avant que ça plante.
Ou mieux: If Ls = 0 Then Ls = 1: Ts(1, 2) = "Rien trouvé"

 

[R@chid]

Re : VBA - Remplacer une formule matricielle par un code

Re,
enfin j'arrive pas, à moins que tu aies l'amabilité de me le faire sur le fichier pour que je puisse bien comprendre.

@ + +

 

[Dranreb]

Re : VBA - Remplacer une formule matricielle par un code

Qu'est ce que tu ne comprends pas ?
Ça plante au Me.[A8].Resize(Ls, UBound(Ts, 2)).Value = Ts si Ls est resté à 0 du fait qu'il n'y avait aucune ligne de Te qui correspondait aux critères. Il suffit de mettre avant ça Ls à 1 pour que ça ne plante plus, et en profiter pour mettre dans la colonne 2 de cette unique ligne de Ts un avis disant que rien n'a été trouvé afin qu'on ne soupçonne pas un disfonctionnement.

 

[R@chid]

Re : VBA - Remplacer une formule matricielle par un code

Re,
merci pour ton retour, je parle du post #8 et aussi le problème avec Me, malgré que j'ai remplacé le Me par Feuil4.

 

[job75]

Re : VBA - Remplacer une formule matricielle par un code

Bonjour R@chid, Bernard,

J'y vais de ma solution avec l'évènement Calculate :

Private Sub Worksheet_Calculate()
Dim nom$, an%, mois As Byte, t, a(), i&, n&
nom = [B1]
an = Year(DateValue("1 " & [B2]))
mois = Month(DateValue("1 " & [B2]))
t = [Tableau1]
ReDim a(1 To UBound(t), 1 To 6)
For i = 1 To UBound(t)
  If Year(t(i, 1)) = an And Month(t(i, 1)) = mois And t(i, 2) = nom Then
    n = n + 1
    a(n, 1) = t(i, 1): a(n, 2) = t(i, 3)
    a(n, 3) = t(i, 4): a(n, 4) = t(i, 5)
    a(n, 5) = t(i, 6): a(n, 6) = a(n, 5) - a(n, 4)
  End If
Next i
'---restitution et mise en forme---
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("A8:F" & Rows.Count).Delete xlUp 'RAZ
If n Then
  [A8].Resize(n, 6) = a
  [F1] = Application.Sum(Application.Index(a, , 6))
  [A8].Resize(n, 6).Borders.Weight = xlThin
End If
Application.EnableEvents = True
End Sub

Fichier joint.

A+

 

[R@chid]

Re : VBA - Remplacer une formule matricielle par un code

Re,
Bonjour job75
merci à toi aussi pour ce code.


@ + +