Discussion :

[thespeedy20]

Bonjour,

Afin de compléter un registre, je désirerai obtenir les dates suivant un jour choisi par liste déroulante pendant une période donnée :

Ex :

Pour la période du 01/09/2021 au 15/10/2021 pour le Jeudi (La période et jour peuvent varier bien sur...)

Cela donnerait :

07/09/2021
14/09/2021
21/09/2021
28/09/2021
04/10/2021
11/10/2021

Je devrais inclure ce code dans mon projet

Je vous en remercie par avance

OLi

[ARTURO83]

Bonjour,

Essayez ceci:
Pièce jointe 606799

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
Sub Recup_Jour()
    Dim Date_Deb As Date, Date_fin As Date
    Dim Jour As String
    Dim i As Long, Lig_Dest As Long
    Application.ScreenUpdating = False
    Date_Deb = Range("B3").Value
    Date_fin = Range("B5").Value
    Jour = UCase(Format(Range("B9").Value, "dddd"))
    Range("G4:G1000").ClearContents
    Lig_Dest = 4
    For i = 0 To 6
        If UCase(Format(Date_Deb + i, "dddd")) = Jour Then
            Cells(Lig_Dest, "G") = Date_Deb + i
            Date_Deb = Date_Deb + i + 7
            Lig_Dest = Lig_Dest + 1
            Do While Date_Deb <= Date_fin
                Cells(Lig_Dest, "G") = Date_Deb
                Date_Deb = Date_Deb + 7
                Lig_Dest = Lig_Dest + 1
            Loop
            Exit For
        End If
    Next i
End Sub

Cdlt

[Pierre Fauconnier]

Salut.

Pas sûr de bien comprendre ton problème. De ton énoncé, je comprends que tu cherches à établir la suite des jeudis entre le 01/09 et le 15/10, or dans ton message, tu donnes une suite de 6 dates dont 4 sont des mardis et 2 des lundis...

Si ta demande consiste à créer la liste des jeudis entre les deux dates, je te propose de penser Excel avant de penser VBA.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Test()
  Dim FirstDay As Date
  Dim DayNum As Long
  Dim Target As Range
 
  Set Target = Range("DébutSérie")
  DayNum = Application.Match(Range("JourChoisi").Value, Array("Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi", "Dimanche"))
  FirstDay = Evaluate("if(weekday(datedébut,2)=" & DayNum & ",datedébut,datedébut-weekday(datedébut," & 10 + DayNum & ")+8)")
  Do While FirstDay <= Range("datefin")
    Target.Value = FirstDay
    Set Target = Target(2)
    FirstDay = FirstDay + 7
  Loop
End Sub

• Nomme tes cellules, surtout pour intégrer ce code dans un projet plus important. Sans cela, c'est vite la galère. Ici, j'ai nommé B3 DateDébut, B5 DateFin, B9 JourChoisi et la première cellule du résultat DébutSérie. A toi d'adapter à ton projet;
• On cherche d'abord le numéro du jour de la semaine choisi avec l'équivalent VBA de la fonction EQUIV en Excel;
• On trouve le premier jour de la série avec une formule Excel;
• On remplit la série tant que le jour calculé est plus petit ou égal au dernier jour souhaité.

Quand on programme en VBA pour Excel, on pense Excel avant de penser VBA...

[thespeedy20]

Bonsoir Arturo83, Pierre

Merci à vous deux pour vos propositions... c'est que je désirais...

@Pierre
Sorry pour les dates, je ne sais comment j'ai fait mon compte, tes explications sont très claires, merci beaucoup...


Pour vos deux propositions, si j'ai des exclusions genre jours fériés ou congés scolaires...

Exemple : pour les vacances de pâques : le jour de recherche est le lundi. La période 01 avril 2022 au 30 avril 2022.
les dates du congé sont du Lundi 04 avril 2022 au 18 avril 2022

donc il faudrait exclure 04/04 - le 11/04 et le 18/04, donc je suppose créer une plage avec toutes les exclusions(même pour les autres jours de recherche)...
mais après ?

Je vous remercie déjà par avance pour votre aide

Bonne soirée

OLi

[ARTURO83]

Bonjour,

Je vous propose avec l'aide d'une MFC, de griser le fond des cellules dont les dates correspondent aux périodes de vacances, bien sûr, il vous faudra auparavant avoir rempli un tableau contenant toutes ces périodes.

Exemple de formule MFC:

Code :

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

1
2
 
=SOMMEPROD(($G4<>"")*($G4>=VAC_DEB)*($G4<=VAC_FIN)*1)

Le fichier avec le tableau des vacances à remplir par vos soins
Pièce jointe 606813

Cdlt

[Philippe Tulliez]

Bonjour Pierre,
Je privilégie également l'usage des fonctionnalités natives d'excel et dans ce cas précis, j'aurais utilisé la méthode DataSeries en lieu et place de la boucle (ligne 9 à 13)
Soit

Code :

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

1
2
3
4
  With Range("DébutSérie")
  .Value = FirstDay
  .DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlDay, Step:=7, Stop:=Range("datefin").Value
  End With

[Pierre Fauconnier]

Si tu ne veux pas reprendre des dates se trouvant dans certaines plages, tu vas devoir créer un tableau des plages d'exclusion, qui sera bien entendu un tableau structuré. Tu pourras alors utiliser une formule qui renvoie 1 lorsque la date est située dans une des plages renseignées. Pour faciliter la saisie de plages, on peut complexifier un peu la formule pour ne mentionner que les dates de début lorsque la plage est constituée d'une seule date.

Voici un exemple de formule basée sur les dates extraites et un tableau des exclusions.




Tu inclus cette évaluation formulée (penser Excel avant de penser VBA ) avec Un Evaluate à l'intérieur de la boucle qui parcourt tes dates:

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
Sub Test()
  Dim FirstDay As Date
  Dim DayNum As Long
  Dim Target As Range
 
  Set Target = Range("DébutSérie")
  DayNum = Application.Match(Range("JourChoisi").Value, Array("Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi", "Dimanche"))
  FirstDay = Evaluate("if(weekday(datedébut,2)=" & DayNum & ",datedébut,datedébut-weekday(datedébut," & 10 + DayNum & ")+8)")
  Do While FirstDay <= Range("datefin")
    If Evaluate("=SUMPRODUCT((((" & FirstDay * 1 & "=Tableau2[Début])*(Tableau2[Fin]=0)+(" & FirstDay * 1 & ">=Tableau2[Début])*(" & FirstDay * 1 & "<=Tableau2[Fin]))>0)*1)") = 0 Then
      Target.Value = FirstDay
      Set Target = Target(2)
    End If
    FirstDay = FirstDay + 7
  Loop
End Sub

[Pierre Fauconnier]

Salut Philippe,

Bonjour Pierre,
Je privilégie également l'usage des fonctionnalités natives d'excel et dans ce cas précis, j'aurais utilisé la méthode DataSeries[...]

Je n'y avais pas pensé. Mais dans la mesure où il faut exclure des dates, je pense qu'on ne va pas pouvoir se passer de la boucle.

[Raccourcix]

Bonjour à tous

je vous propose d'utiliser les fonctions "xxx.JOURS.OUVRES.INTL" qui ont la particularité de pouvoir gérer à la fois des week-ends personnalisés et des jours fériés

le troisième argument de ces deux fonctions NB.JOURS.OUVRES.INTL et SERIE.JOURS.OUVRES.INTL est par défaut un nombre de 1 à 7 et de 11 à 17 mais il est possible de personnaliser le week-end avec une série de 7 caractères 1 ou 0 (1 = WE et 0 = jour ouvré)
une semaine "normale" c'est donc "0000011" soit cinq jours ouvrés= zéros du lundi au vendredi et deux 1 pour samedi et dimanche

L'idée ici est de mettre que le jour choisi en 0
si on veut obtenir que les jeudis li faut utiliser : "1110111"

donc pour connaitre le nombre de jeudis entre deux dates avec une liste de jours Fériés/Vacances

Code :

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

=NB.JOURS.OUVRES.INTL(date_début;date_fin;"1110111";Fériés_Vacances)

et pour obtenir le Nième jeudi

Code :

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

SERIE.JOUR.OUVRE.INTL(date_début;N;"1110111";Fériés_Vacances)

une fonction SEQUENCE ou LIGNE(INDIRECT("1:N")), ou encore une boucle en VBA permet ainsi d'obtenir la liste des jeudis

pour créer cette chaîne de caractères "1110111" :

Code :

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

=JOINDRE.TEXTE(;;N(Liste_Jour<>jour_choisi))

Voir le fichier joint

Stéphane

[thespeedy20]

Bonsoir à tous,

Je vous remercie tous vos propositions, j'y regarde une après l'autre pour comprendre...

@Pierre

J'ai remis dans un fichier ton code, mais je n'obtiens pas les bons résultats... sur le dimanche j'ai même incompatibilité de type, les dates affichées ne correspondent pas au jour choisis...
j'ai du mal à comprendre ou se trouve l'erreur...

Bien à toi

OLi

[Pierre Fauconnier]

Désolé, j'avais une erreur dans la formule MATCH, il manquait le , 0 à la fin du MATCH pour chercher la valeur exacte

La bonne ligne est : DayNum = Application.Match(Range("JourChoisi").Value, Array("Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi", "Dimanche"), 0)
A priori, il faudrait aussi vider la plage de résultat au cas où tu réduirais l'amplitude des dates.

[thespeedy20]

Bonjour à tous,

@Pierre
Merci beaucoup, cela fonctionne très bien maintenant...

@Raccourcix
Merci également pour ta proposition qui fonctionne très bien également avec une autre approche...

@ARTURO83
Merci aussi avec ton approche MFC qui me servira pour mettre en évidence les congés, et jours fériés.

@Philippe Tulliez
Merci Philippe pour ton intervention dans ce poste...


OLi