Condition VBA J-4 sans les week end

Version imprimable

11/06/2012, 16h34
Naoned005

1 pièce(s) jointe(s)

Condition VBA J-4 sans les week end

Bonjour

Dans le fichier si joint j'ai joins un exemple c'est toujours plus simple qu'un grand discours. :D

En fait je cherche a sommer sous condition en VBA et une des condition est

For i = 2 To 10000

If aujourd'hui()-4 sans compter les week end Then ect ect

Mais je ne vois pas comment définir aujourd'hui -4 sans compter les week end:aie:
11/06/2012, 17h00
Godzestla

Ben il faut réfléchir.

Si entre aujourd'hui (peut aussi être un week-end) et aujourd'hui moins 4, il y un week-end ou une partie de week-end, il faut enlever le ou les jours de week-end. et donc ralonger le -4 en ce qu'il faut.

Moralité.

C'est de la logique.

Tu pars d'aujourd'hui.
Tant que tu n'as pas compté y compris aujourd'hui et en reculant 5 jours hors week-end, tu recules.

Pas de formule toute faite dans excel pour cela. Ettttt non !!!
11/06/2012, 17h18
Philippe Tulliez

Bonjour,

Citation:

Pas de formule toute faite dans excel pour cela. Ettttt non !!!

A moins de ne pas avoir compris la question. La formule Excel SERIE.JOUR.OUVRE() , WORKDAY() en version anglaise permet de donner une date en fonction d'une date de départ et d'un écart jour.
Donc pour calculer la date d'aujourd'hui -4 jours sans les week-end
Exemple en Excel :

Code:

=SERIE.JOUR.OUVRE(AUJOURDHUI();-4)

Exemple en VBA avec WorskSheetFunction

Code:

Debug.Print Format(Application.WorksheetFunction.WorkDay(Date, -4), "ddd dd/mm/yy")

Le troisième argument de cette fonction permet de référencer une plage, contenant les jours fériés.
Il est possible qu'il existe son équivalent en fonction date de VBA mais je n'ai pas le temps de chercher
11/06/2012, 17h23
Naoned005

Bonjour

La macro ne marchera que les jours ouvrés donc quand on la lance on peut pas etre le week end. Une des conditions pour faire ma somme est que sur la case date elle doit pas etre supérieur a J-4

Par exemple on est lundi 11 juin, il faut prendre vendredi 8 jeudi 7 et mercredi 6

Si on est mardi 12 juin, il faut prendre jeudi 7 vendredi 8 et lundi 11

Si on est mercredi 13 juin, il faut prendre vendredi 8 lundi 11 et mardi 12

Et je ne vois toujours pas comment faire.

Mon objectif est de faire une somme apres plusieurs filtres.

Mon premier filtre est de selectionner seulement les lignes qui ont dans la colonne A une date entre aujourd'hui et aujourd'hui()-4 SANS COMPTER les week end.
11/06/2012, 17h33
Philippe Tulliez

Bonjour,
Ta question était

Citation:

Mais je ne vois pas comment définir aujourd'hui -4 sans compter les week end

J'ai répondu par rapport à cela.
11/06/2012, 17h39
Naoned005

Merci Corona:

Tu penses alors que je peux faire ça pour additionner tous les nombres de la colonne P qui date d'aujourd'hui a aujourd'hui -4 sans compter les week end?

Code:

=SOMME.SI('Daily Equity'!A3:P289;OU('Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-4);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-3);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-2);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-1);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();0));'Daily Equity'!P3:P89)

??
11/06/2012, 17h55
Philippe Tulliez

Bonjour,
Voici un exemple avec SOMMEPROD en Excel. pour des dates qui se trouvent en plage $B$1:$L$1

Code:

=SOMMEPROD( (B$1:L$1 >= SERIE.JOUR.OUVRE(AUJOURDHUI();-4)) * (B$1:L$1 <= AUJOURDHUI()) * (JOURSEM($B$1:$L$1;2)<6) * $B$2:$L$2)

Un peu de lecture sur les fonctions Date et là aussi
12/06/2012, 09h02
Godzestla

Citation:

Bonjour,
Citation:
Pas de formule toute faite dans excel pour cela. Ettttt non !!!
A moins de ne pas avoir compris la question.

Sorry Philippe, Worday n'est pas implémenté sous Excel 2003. (donc je ne connaissais pas).

Mais très intéressant !!
12/06/2012, 09h24
Philippe Tulliez

Bonjour,

Citation:

Envoyé par Godzestla

Sorry Philippe, Worday n'est pas implémenté sous Excel 2003. (donc je ne connaissais pas).
Mais très intéressant !!

Il n'y a pas de mal on ne peut pas tout connaître.
Cette formule existe également dans la version 2003 en activant l'utilitaire d'analyse (Outils/Macros complémentaires... cocher Utilitaire d'analyse)
12/06/2012, 09h40
Godzestla

Joli. :ccool:

je viens de l'activer.

Merci beaucoup Philippe.
12/06/2012, 12h10
Naoned005

Merci interessant. Mais en fait je ne vois pas trop à quoi cela peut t-il me servir! :lol:

Je rééxplique mon probleme (tres tres tres :)) synthetiquement:

J'ai une liste avec en colonne A des dates sur une feuille excel (jusqu'a là ça va) rangée par ordre croissant (jusqu'ici aussi).

Mon objectif est de faire une macro qui calcule des quantités présentes un peu plus loin sur la même feuille excel. J'ai plusieurs conditions mais celle que je n'arrive pas a rentrer c'est celle qui dis en VBA (je pense que c'est le seul moyen de faire non?)) de selectionner que les lignes qui ont dans la colonne A ont une date qui correspond a celle d'aujourd'hui, d'hier , d'avant hier , d'avant avant hier (bref jusqu'a J-4 en comptant aujourd'hui :)) sans compter les week end? C'est possible? :aie:

Merci d'avance!! :lol:

Bonjour,
Je t'ai donné une solution par excel parce-que tu m'as donné un exemple en Excel avec

Citation:

Tu penses alors que je peux faire ça pour additionner tous les nombres de la colonne P qui date d'aujourd'hui a aujourd'hui -4 sans compter les week end?

Code:

=SOMME.SI('Daily Equity'!A3:P289;OU('Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-4);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-3);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-2);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();-1);'Daily Equity'!A3:A289=SERIE.JOUR.OUVRE(AUJOURDHUI();0));'Daily Equity'!P3:P89)

Tu semblais dire que ton seul problème était de savoir comment calculer la date du jour - 4 jours. Solution que je t'ai donné.
Il semble finalement que ton problème est ailleurs
Si réellement tes dates sont sur une colonne, ton problème est plus simple que tu l'avais décrit.
Voici une solution parmi d'autres
On suppose que les dates sont en $A$2:$A$12 et les nombres à sommer de $D$2:$H$12
1) La procédure parcourt les lignes de 2 à 12, (For row = 2 To 12)
2) Vérification par un Select Case si la valeur dans la cellule est comprise entre Aujourd'hui et aujourd'hui - 4 ET que le jour est inférieur à 6 cad samedi, (Case dayWrk To Date And Application.WorksheetFunction.Weekday(cel, 2) < 6)
3) Si la condition est vrai, on fait le total et on l'affiche par un Debug.Print
Je suppose que tu décoderas aisément ce que chaque variable signifie.
Exemple ci-dessous
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Dim sht As Worksheet, rng As Range, cel As Range Dim row As Long, col As Long Dim total As Double Dim dayWrk As Date dayWrk = Application.WorksheetFunction.WorkDay(Date, -4) ' Aujourd'hui - 4 Set sht = ThisWorkbook.Worksheets("Feuil2") For row = 2 To 12 Set cel = sht.Range("A" & row) Set rng = sht.Range("D" & row, "H" & row) 'Debug.Print Format(cel, "ddd dd/mm/yy") & vbCrLf & Format(Application.WorksheetFunction.WorkDay(Date, -4), "ddd dd/mm/yy") Select Case cel Case dayWrk To Date And Application.WorksheetFunction.Weekday(cel, 2) < 6 total = Application.WorksheetFunction.Sum(rng) Debug.Print cel & vbTab & total End Select Next
Exemple à adapter selon tes besoins.

aH MERCI

C'est trop cool. C'est ce que je cherchais. Je l'essaye sur mon ordi et apres je rajoute les autres conditions que j'ai a rajouté avant de sommer.

Merci encore :ccool:

Slt je pense que ton code fonctionne bien car dans la fenetre d'execution il prend a partir du 7 juin (ce qui correspond à J-4) mais j'ai rajouté un truc pour qu'il affiche le resultat sur la feuille excel et j'ai l'impression que ton code renvoie la valeur du dernier chifffre inscrit dans la colonne a sommer ;)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Sub somme() Dim sht As Worksheet, rng As Range, cel As Range Dim row As Long, col As Long Dim total As Double Dim dayWrk As Date dayWrk = Application.WorksheetFunction.WorkDay(Date, -4) ' Aujourd'hui - 4 Set sht = ThisWorkbook.Worksheets("Daily Equity") For row = 3 To 80 Set cel = sht.Range("A" & row) Set rng = sht.Range("P" & row) 'Debug.Print Format(cel, "ddd dd/mm/yy") & vbCrLf & Format(Application.WorksheetFunction.WorkDay(Date, -4), "ddd dd/mm/yy") Select Case cel Case dayWrk To Date And Application.WorksheetFunction.Weekday(cel, 2) < 6 total = Application.WorksheetFunction.Sum(rng) 'Debug.Print cel & vbTab & total Range("B" & 82).Value = total End Select Next End Sub

12/06/2012, 15h22
Philippe Tulliez
Bonjour,
Dans ma proposition, la variable cel est une cellule t rng est une plage
Code:

1 2 Set cel = sht.Range("A" & row) Set rng = sht.Range("D" & row, "H" & row)
or toi, tu références les deux variables comme une cellule
Code:

1 2 Set cel = sht.Range("A" & row) Set rng = sht.Range("P" & row)
Alors évidemment faire la somme d'une cellule cela équivaut à lui attribuer la même valeur

Code:

total = Application.WorksheetFunction.Sum(rng)

Je pense que le problème vient de là
12/06/2012, 15h57
Naoned005

Citation:

faire la somme d'une cellule cela équivaut à lui attribuer la même valeur

Je ne veux pas la somme d'une cellule mais des celulles de P3 à P80 qui ont une date (dans la colonne A) inférieur ou égales a aujourd'hui-4.

Mais je vois ce que tu veux dire, en fait arrivé a la boucle 80 rng=80 et donc total=80.

Je pense qu'il faudrait crée une variable (somme par exemple) intermédiaire qui stocke et additionne a chaque fois le rng si il rempli la condition du weekday?

une sorte d'incrémentation?
12/06/2012, 16h10
Philippe Tulliez

Bonjour,

Citation:

Envoyé par Naoned005

Je ne veux pas la somme d'une cellule mais des celulles de P3 à P80 qui ont une date (dans la colonne A) inférieur ou égales a aujourd'hui-4.
Mais je vois ce que tu veux dire, en fait arrivé a la boucle 80 rng=80 et donc total=80.
Je pense qu'il faudrait crée une variable (somme par exemple) intermédiaire qui stocke et additionne a chaque fois le rng si il rempli la condition du weekday?

Je n'ai pas le sentiment que tu lises complètement ce que j'écris.
La variable objet rng doit être une plage donc si tu veux sommer les cellules de P3 à P80, il faut faire

Code:

Set rng = sht.Range("P2:P80")

mais à la lecture de ton dernier post, j'ai l'impression que nous ne parlons pas de la même chose.
D'après ta dernière demande, tu dis que les dates sont en colonne A, j'ai donc penser qu'il fallait faire la somme de valeurs se trouvant sur la même ligne mais dans des colonnes différentes.
Il serait intéressant que tu expliques comment est organisé ton tableau.

Bonjour,
J'ai finalement ouvert le classeur que tu as déposé.
Très sincèrement, s'il s'agit de faire une synthèse (tableau de bord) dans la feuille [Rapport DEXIA], en faisant la somme de données se trouvant dans la feuille [Daily Equity] et comprise entre entre aujourd'hui et aujourd'hui -4 et ce avec éventuellement d'autres critères, il n'y a aucune raison d'utiliser du VBA.
La formule SOMMEPROD fait cela très bien. Inspire-toi de l'exemple que je t'ai donné

1 pièce(s) jointe(s)

Merci

Je t'envoie le tableau en PJ. J'aimerais sommer les quantités dans la colonne P quand en A j'ai une date entre aujourd'hui et aujourd'hui-4 :)

J'ai essayé le "nouveau code"

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub Rappro()
 
 
 
Dim sht As Worksheet, rng As Range, cel As Range
 Dim row As Long, col As Long
 Dim total As Double
 Dim dayWrk As Date
 
 
 dayWrk = Application.WorksheetFunction.WorkDay(Date, -4) ' Aujourd'hui - 4
 Set sht = ThisWorkbook.Worksheets("Daily Equity")
 For row = 3 To 80
  Set cel = sht.Range("A" & row)
  Set rng = sht.Range("P3:P80")
  'Debug.Print Format(cel, "ddd dd/mm/yy") & vbCrLf & Format(Application.WorksheetFunction.WorkDay(Date, -4), "ddd dd/mm/yy")
  Select Case cel
   Case dayWrk To Date And Application.WorksheetFunction.Weekday(cel, 2) < 6
 
 
    total = Application.WorksheetFunction.Sum(rng)
    'Debug.Print cel & vbTab & total
 
    Range("B" & 82).Value = total
 
 
  End Select
 Next
 
End Sub

mais il somme tout!!! meme les ligne avec des dates plus anciennes :(

Bonjour,
C'est normal qu'il fait la même somme, puisqu'en parcourant les dates de la colonne A de 3 à 80, chaque fois la somme est faite sur P3:P80. Nous ne parlions pas de la même chose.
En fait si vraiment tu veux utiliser du code VBA plutôt que la formule SOMMEPROD il faut utiliser le code ci-dessous
Finalement la variable rng est bien "P" & Row et le calcul du total est : total = total + rng
Code:

1 2 3 4 5 6 7 8 9 10 For row = 3 To 80 Set cel = sht.Range("A" & row) Set rng = sht.Range("P" & row) 'Debug.Print Format(cel, "ddd dd/mm/yy") & vbCrLf & Format(Application.WorksheetFunction.WorkDay(Date, -4), "ddd dd/mm/yy") Select Case cel Case dayWrk To Date And Application.WorksheetFunction.Weekday(cel, 2) < 6 total = total + rng ' <<<<<<< End Select Next Debug.Print "Total = " & Total
Bonjour,
Ce qui se traduit avec la formule SOMMEPROD par

Code:

=SOMMEPROD( (A$3:A$80 >= SERIE.JOUR.OUVRE(AUJOURDHUI();-4)) * (A$3:A$80 <= AUJOURDHUI()) * (JOURSEM($A$3:$A$80;2)<6) * $P$3:$P$80)

12/06/2012, 16h53
Naoned005

MERCI

JE M'incline ça marche, j'ai remplacé ta formule en ligne par une en colonne. Je ne comprend pas pourquoi ça marche mais ça marche

oui je ne vois pas du tout ce que viens faire une sommeprod la dedans.

Je l'utilise normalement pour les vecteurs mais des dates!!! ça m'est obscure
12/06/2012, 17h16
Philippe Tulliez

Bonjour,
C'est une utilisation détournée de SOMMEPROD.
Tu effectues plusieurs tests logiques de comparaisons sur une plage qui renvoient des VRAI et des FAUX
Par exemple :
( A$3:A$80 >= SERIE.JOUR.OUVRE(AUJOURDHUI();-4) )
La formule SOMMEPROD crée une matrice de VRAI.FAUX que tu multiplies par une autre matrice de VRAI.FAUX constituée d'une autre comparaison comme ( A$3:A$80 <= AUJOURDHUI() ) et en final on multiplie par une valeur.
VRAI par VRAI = 1 et VRAI par FAUX = 0 si le résultat final est 1 que l'on multiplie ensuite par une valeur par exemple 112 on aura donc 112 (résultat du produit) et ensuite SOMMEPROD fait la somme de ces produits.

Pour visualiser tu sélectionnes un des tests logiques et tu tapes sur la touches de fonction F9. Attention de faire Escape/Echappement après.

Je te conseille le tuto de Pierre Fauconnier sur le sujet.

Citation:

oui je ne vois pas du tout ce que viens faire une sommeprod la dedans.
Je l'utilise normalement pour les vecteurs mais des dates!!! ça m'est obscure

Une date est un nombre qui est un numéro de série dont la valeur 1 représente le 1er janvier 1900 tout au moins pour les versions Windows d'excel

1 pièce(s) jointe(s)

AHH ok merci je vais regarder ça vraiment en détail. Donc maintenant dans mon petit fichier j'ai ça. (cf plus bas)

J'aurais voulu savoir si il est possible de jumelé cette formule avec les 2 autres conditions (bien plus simple je pense) suivantes (ou bien il y a besoin de VBA)?:

On additionne toujours d'aujourd'hui a aujourd'hui-4 (dans le carré que j'ai mi en bleu dans mon fichier) mais il y a 2 autres conditions restreignantes

1) il faurait (pour additionner) que dans la colonne B de l'onglet DAILY EQUITY il y ai marqué "Financière Capital +"

2) il faudrait (aussi pour additionner) que dans la colonne E de l'onglet DAILY EQUITY il y est le même "code isin" que celui correspondant à la case que nous voulons calculer. Je m'explique quand on veut calculer la case en bleu j'ai mi le code ISIN en bleu, quand on veut calculoer le case en vert on prend le code ISIN en vert ect ect

Tu vois ce que je veux dire ou je ne suis pas clair du tout?

mERCI ENCORE:ccool:

Je pensais a un code VBA dans ce style mais bon c'est surement n'importe quoi :lol:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Sub tt() For m = 3 To 78 For b = 13 To 37 If DailyEquity!Range("B" & m).Value = "Financière Capital +" AND Daily Equity!Range("E" & m).Value = 'Rappro Dexia!Range("B" & b) Then ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT( ('Daily Equity'!R[-10]C[-15]:R[65]C[-15] >= WORKDAY(TODAY(),-3)) * ('Daily Equity'!R[-10]C[-15]:R[65]C[-15] <= TODAY()) * (WEEKDAY('Daily Equity'!R[-10]C[-15]:R[65]C[-15],2)<6) * 'Daily Equity'!R[-10]C:R[65]C)" Range("P14").Select End If Next Next End Sub

12/06/2012, 20h47
Philippe Tulliez

Bonsoir,

Citation:

Envoyé par Naoned005

AHH ok merci je vais regarder ça vraiment en détail. Donc maintenant dans mon petit fichier j'ai ça. (cf plus bas)
J'aurais voulu savoir si il est possible de jumelé cette formule avec les 2 autres conditions (bien plus simple je pense) suivantes (ou bien il y a besoin de VBA)?:
On additionne toujours d'aujourd'hui a aujourd'hui-4 (dans le carré que j'ai mi en bleu dans mon fichier) mais il y a 2 autres conditions restreignantes
1) il faurait (pour additionner) que dans la colonne B de l'onglet DAILY EQUITY il y ai marqué "Financière Capital +"
2) il faudrait (aussi pour additionner) que dans la colonne E de l'onglet DAILY EQUITY il y est le même "code isin" que celui correspondant à la case que nous voulons calculer. Je m'explique quand on veut calculer la case en bleu j'ai mi le code ISIN en bleu, quand on veut calculoer le case en vert on prend le code ISIN en vert ect ect
Tu vois ce que je veux dire ou je ne suis pas clair du tout?
mERCI ENCORE:ccool:

As-tu consulté le lien que je t'ai donné et as-tu lu ce que j'ai écris dans ce fil par rapport à la formule SOMMEPROD, tu peux ajouter toutes les conditions que tu souhaites. La syntaxe est

Code:

SOMMEPROD( (Condition1) * (Condition2) * (Condtion3) * (ConditionX) * Valeurs)

.
Restriction : Dans les conditions, si tu fais référence à des plages avec lignes et colonnes. Tu dois avoir le même nombre de lignes ou colonnes dans les autres conditions.
Si tu fais une recherche sur Internet en tapant Excel SOMMEPROD, tu vas avoir assez d'exemples pour t'en sortir.