Condition VBA J-4 sans les week end

**Naoned005** · 11/06/2012, 16h34

Bonjour

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

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

**Godzestla** · 11/06/2012, 17h00

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 !!!

**Philippe Tulliez** · 11/06/2012, 17h18

Bonjour,

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 :

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

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

Exemple en VBA avec WorskSheetFunction

Code :

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

 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

**Naoned005** · 11/06/2012, 17h23

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.

**Philippe Tulliez** · 11/06/2012, 17h33

Bonjour,
Ta question était

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

J'ai répondu par rapport à cela.

**Naoned005** · 11/06/2012, 17h39

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 :

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

=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)

??

**Philippe Tulliez** · 11/06/2012, 17h55

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

Code :

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

=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

**Godzestla** · 12/06/2012, 09h02

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 !!

**Philippe Tulliez** · 12/06/2012, 09h24

Bonjour,

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)

**Godzestla** · 12/06/2012, 09h40

Joli.

je viens de l'activer.

Merci beaucoup Philippe.

**Naoned005** · 12/06/2012, 12h10

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

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?

Merci d'avance!!

**Philippe Tulliez** · 12/06/2012, 12h50

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

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 :

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

=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 :

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
 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.

**Naoned005** · 12/06/2012, 14h10

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

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 :

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
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

**Philippe Tulliez** · 12/06/2012, 15h22

Bonjour,
Dans ma proposition, la variable cel est une cellule t rng est une plage

Code :

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

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 :

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

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 :

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

total = Application.WorksheetFunction.Sum(rng)

Je pense que le problème vient de là

**Naoned005** · 12/06/2012, 15h57

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?

**Philippe Tulliez** · 12/06/2012, 16h10

Bonjour,

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 :

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

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é

**Naoned005** · 12/06/2012, 16h20

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 :

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
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

**Philippe Tulliez** · 12/06/2012, 16h31

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 :

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

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 :

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

=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)

**Naoned005** · 12/06/2012, 16h53

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

**Philippe Tulliez** · 12/06/2012, 17h16

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.

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