XL 2010 Fonction matricielle en vba...

[jeanba]

Bonjour à tous et à toutes!
Meilleurs voeux pour 2017!

Je suis à la recherche du code pour cette formule qui marche très bien sous excel mais qui bouffe énormément de ressources et du coup ralentit mon projet.

- La feuille RECAP est celle de la formule. Les seules références utiles dans cette feuille sont 2 références absolues ( une fixe en $A$1 et l'autre mobile en fonction de la colonne de calcul, mais la ligne figée est $5 à partir de la colonne C).

- Centralisation est le tableau de recherche

Je voudrais renseigner mon tableau avec du code pour gagner en temps d'exec..
Merci beaucoup!

Jeanba

 

[Lolote83]

Salut JeanBa,
Pour commence, bonne année à toi.
Ensuite, un fichier joint serait plus utile pour faire des tests.
Peut être en modifiant ta formule matricielle par un SOMMEPROD (non matriciel) pourrait peut être faire mieux (quoi que !!!)
Pour finir, si ta plage, ne prend pas en compte l'intégralité d'une colonne mauis juste les lignes necessaires, on pourrait aussi gagner en ressource. C'est à dire, au lieu de prendre la colonne A dans son intégralité A:A, prendre par exemple A1:A100
@+ Lolote83

 

[jeanba]

Bonjour Lolote83,

Tes remarques sont pertinentes.
Voici le fichier ci-joint pour les tests en effet.
Pour ce qui est de la colonne entière, en fait, c'est jusqu'à la dernière cellule non vide.
Mais, comme je sais pas le dire avec SOMMEPROD mais seulement avec RECHERCHE(9^9;...), du coup j'ai mis colonne entière.

Merci d'avance

Jeanba

 

[Dranreb]

Bonjour.
Cette macro me parait pas mal :

Sub CalculerRecap()
Dim TE(), LE&, TS(1 To 31, 1 To 15), LS&, CS&, Mois#
TE = Centralisation.UsedRange.Value
Mois = RecapMois.[A1].Value
For LE = 3 To UBound(TE, 1)
   If TE(LE, 1) = Mois Then
      LS = TE(LE, 2): CS = TE(LE, 5)
      TS(LS, CS) = TS(LS, CS) + TE(LE, 6) - TE(LE, 7)
      End If: Next LE
RecapMois.[C6:Q36].Value = TS
End Sub

 

[jeanba]

Salut Dranreb,

Toi tu cesseras jamais de me surprendre avec ta rapidité et toujours du code très soft, mais efficace!
Ton code a l'air de fonctionner pas mal, seul hic, il met des "-" devant certaines valeurs

Si tu pensais mettre les sorties en (-), ben, c'est pas mal mais à ce moment là, faudrait que toutes tes valeurs de sorties soient ainsi.
mais, en termes de présentation, c'est peut-être pas ça le top je crois...

 

[Dranreb]

Si ce n'est pas bon il suffit d'ajouter TE(LE, 7) au lieu de le soustraire. Et soustraire l'autre, TE(LE, 6) ?

 

[jeanba]

Non, j'ai déjà essayé ça.
Le problème ne concerne pas toutes les colonnes. Avant d'inverser l'ordre dans la formule, c'était sur la colonne 1 du tableau (celle du Libellé 1) qu'il y avait souci. Après inversion, toutes les colonnes sont maintenant concernées sauf la colonne 2 du tableau.
Peux-tu mettre des commentaires pour que je me retrouve dans tes variables s'il te plaît et ainsi pourvoir comprendre pourquoi on a cette erreur

 

[Dranreb]

Mais… quelle erreur ?
Mets des commentaires toi pour te rappeler ce que tu ne comprends pas.
Moi je prends toujours les mêmes noms de variables avec T pour tableau, L pour ligne, C pour colonne, E pour entrée et S pour sortie.

 

[jeanba]

Bah, c'est juste ce précision qu'il me fallait
Du coup, l'erreur dans la formule c'est bien le (-). Il fallait laisser tout à (+) puisque, de toutes les façons, les lignes sont soit de sorties, soit d'entrée, jamais les 2 en même temps sur la même ligne...
Merci beaucoup à toi Dranreb et à tous!

Problème résolu (je sais plus où il faut cliquer pour signaler cela...!)

Jeanba

 

[jeanba]

Sub CalculerRecap()
'=SOMME(SI((Centralisation!$A$3:$A$650=RECAP!$A$1)*(Centralisation!$B$3:$B$650=RECAP!$B8) _
    ' *(Centralisation!$E$3:$E$650=RECAP!Q$5);Centralisation!$F$3:$G$650))
Dim TE(), LE&, TS(1 To 31, 1 To 25), LS&, CS&, Mois#
TE = Centralisation.UsedRange.Value
Mois = RecapMois.[A1].Value
For LE = 3 To UBound(TE, 1)
    If TE(LE, 1) = Mois Then
        LS = TE(LE, 2): CS = TE(LE, 5)
        TS(LS, CS) = TS(LS, CS) + TE(LE, 6) + TE(LE, 7)
    End If: Next LE
RecapMois.[C6:AA36].Value = TS
End Sub

 

[jeanba]

Zut!

Dranreb,

Appliquer toute seule, ta macro fonctionne d'équerre!
Mais, lorsque je l'exécute dans une suite de procédures Call, j'ai ce message d'erreur:

Je vais faire des recherches sur ce message mais, si tu avais une idée d'où cela pouvait-il venir...

 

[jeanba]

Ligne générant ce message d'erreur:

 

[Dranreb]

Ça ne peut venir à mon avis que d'un numéro de ligne en colonne 2 de la source (Jour) non compris entre 1 et 31
ou d'un numéro de colonne en colonne 5 de la source (Code imput) non compris entre 1 et 25
Parce que c'est moins probable que se soit la source qui soit renseignée sur moins de 7 colonnes.

Pour le mois 12 il y a un Code imput non renseigné à la ligne 83.

 

[jeanba]

J'ai corrigé l'absence de mention en L83 et vérifier qu'il n'y a pas de valeur <1 et >31 dans la colonne Jour, ni de valeur <1 et >25 dans les Codes Imput..
Et malgré ces contrôle l'exécution continue de générer un message d'erreur pour le mois 12 uniquement.
Ceci est une curiosité!

 

[jeanba]

Dranreb,
J'ai trouvé le souci. Tu as vu juste sur la ligne 83.
J'y ai mis le code 22 pour corriger l'erreur de saisie au lieu de 2..!
C'est bon partout!
Merci A+