Option Explicit
Sub QuiEstDispo()
Dim ValeurRecherche, RangePlage
Dim NomdeProf, RangePlage1
Dim FeuilPlage, SheetsPlage
Dim Début As Range, Fin As Range, Jour As Variant
Dim Column As Range, Rows As Range
Dim MonDicoDeProfs As Variant
Dim I As Integer
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual 'c'est pour que la macro ne rame pas
End With
Jour = Worksheets("Cours").Range("H15").Value 'jour qui nous intéresse pour connaître la dispo du prof - la cellule présente une liste déroulante de Lundi à Samedi
Select Case Jour
Case "Lundi": Column = 3 ' dans le tableau des profs, le lundi correspond à la colonne C -donc 3
Case "Mardi": Column = 4
Case "Mercredi": Column = 5
Case "Jeudi": Column = 6
Case "Vendredi": Column = 7
Case "Samedi": Column = 8
End Select
Début = Worksheets("Cours").Range("I15") 'début de la plage horaire qui nous intéresse pour connaître la dispo du prof
Select Case Début
Case "08:00:00": Rows = 4
Case "08:30:00": Rows = 5
Case "09:00:00": Rows = 6
Case "09:30:00": Rows = 7
Case "10:00:00": Rows = 8
Case "10:30:00": Rows = 9
Case "11:00:00": Rows = 10
Case "11:30:00": Rows = 11
Case "12:00:00": Rows = 12
Case "12:30:00": Rows = 13
Case "13:00:00": Rows = 14
Case "13:30:00": Rows = 15
Case "14:00:00": Rows = 16
Case "14:30:00": Rows = 17
Case "15:00:00": Rows = 18
Case "15:30:00": Rows = 19
Case "16:00:00": Rows = 20
Case "16:30:00": Rows = 21
Case "17:00:00": Rows = 22
Case "17:30:00": Rows = 23
Case "18:00:00": Rows = 24
End Select
Fin = Worksheets("Cours").Range("J15") ' fin de la plage horaire qui nous intéresse pour connaître la dispo du prof
Select Case Fin
Case "08:00:00": Rows = 4
Case "08:30:00": Rows = 5
Case "09:00:00": Rows = 6
Case "09:30:00": Rows = 7
Case "10:00:00": Rows = 8
Case "10:30:00": Rows = 9
Case "11:00:00": Rows = 10
Case "11:30:00": Rows = 11
Case "12:00:00": Rows = 12
Case "12:30:00": Rows = 13
Case "13:00:00": Rows = 14
Case "13:30:00": Rows = 15
Case "14:00:00": Rows = 16
Case "14:30:00": Rows = 17
Case "15:00:00": Rows = 18
Case "15:30:00": Rows = 19
Case "16:00:00": Rows = 20
Case "16:30:00": Rows = 21
Case "17:00:00": Rows = 22
Case "17:30:00": Rows = 23
Case "18:00:00": Rows = 24
End Select
Set MonDicoDeProfs = CreateObject("Scripting.Dictionary") 'pour me donner le nom des profs qui correspondent aux crières
RangePlage = Range(Cells(Début, Jour), Cells(Fin, Jour)).Address
NomdeProf = Cells(1, 5).Value
FeuilPlage = Range(Sheets(3), Sheets.Count).Address
For Each ValeurRecherche In Application.Sheets(SheetsPlage).Range(RangePlage)
If Not MonDicoDeProfs.Exists(NomdeProf.Value) And ValeurRecherche.Value = "" And Cells.Interior.Pattern <> xlSolid Then
MonDicoDeProfs.Add NomdeProf.Value, NomdeProf.Value
End If
Next ValeurRecherche
MsgBox = (Application.Transpose(MonDicoDeProfs.Items)) 'me donne le nom des profs sous forme de msgbox
End Sub