Untitled 4/21/2018 11:43:15 PM
<p>Sub AnalyseBlancsVACA()
'Cette macro analyse les candidatures d'un affichage selon l'application de l'article 19.09
'de la convention collective des fonctionnaires municipaux pour les affichages VACA et VPERM
'Définition des variables
Dim cl1 As Workbook
Dim ComptElig As Long
Dim comptCandidats As Long
Set cl1 = ActiveWorkbook
Call Table_No_VACA19_AL3
Call Table_PMED
'Création de la feuille Analyse
Sheets("Sheet1").Copy Before:=Sheets(1)
ActiveSheet.Name = "Analyse"
'Suppression des lignes d'entête 1 à 8 provenant de InfoRH
Rows("1:8").Select
Selection.Delete Shift:=xlUp
Range("A65536").End(xlUp).Select
Selection.Delete Shift:=xlUp
Range("A65536").End(xlUp).Select
Selection.Delete Shift:=xlUp
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
Columns("P:P").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
'Columns("O:Q").Select
'Selection.Delete Shift:=xlToLeft
'Ajout / nomme des colonnes pour l'analyse des candidatures et mise en forme
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Base"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Étape Réussie"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Admissibilité"
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Éligibilité"
Sheets("Analyse").Select
Range("O1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
End With
ActiveCell.FormulaR1C1 = "Pmed?"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("P1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
End With
ActiveCell.FormulaR1C1 = "'12"
With ActiveCell.Characters(Start:=1, Length:=2).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("Q1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
End With
ActiveCell.FormulaR1C1 = "P%?"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("R1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
End With
ActiveCell.FormulaR1C1 = "T%?"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("S1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
End With
ActiveCell.FormulaR1C1 = "recherchev(stxt("
With ActiveCell.Characters(Start:=1, Length:=16).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
'Détermine le numéro de service équivalent à l'acronyme inscrit dans le numéro d'affichage
Sheets("Base").Select
Range("A3:Q3").Select
Sheets("Analyse").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(MID(Base!R[2]C[-18],40,FIND(""-"",Base!R[2]C[-18])-40),ACCRO,2,FALSE)"
Range("T1").Select
ActiveCell.FormulaR1C1 = _
"=MID(Base!R[2]C[-19],40+LEN(MID(Base!R[2]C[-19],40,FIND(""-"",Base!R[2]C[-19])-40))+5+(IF(ISERROR(FIND(""VPERM"",Base!R[2]C[-19])),4,5)),6)"
Range("U1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Qualifié?"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("V1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Annexe L-3"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("W1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Concatener"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("X1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Étape 8"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("Y1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Étape"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
'Défini le nombre de ligne à considérer dans l'onglet Éligibilité
Sheets("Éligibilité").Select
ComptElig = ActiveSheet.UsedRange.Rows.Count - 2
Range("G8").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Concatener"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
'Concatenation du numero de matricule candidat et du code d'emploi du BAEL
'pour créer une table de recherche
Range("G9").Select
Range("G9:G" & ComptElig).Formula = "=CONCATENATE(RC[-6],RC[-3])"
ActiveWorkbook.Names.Add Name:="ELIG", RefersToR1C1:= _
"=Éligibilité!R8C7:R" & ComptElig & "C7"
'Début de l'analyse des candidatures selon les critères de chacune des colonnes
'ajoutées à la feuille Analyse
Sheets("Analyse").Select
comptCandidats = ActiveSheet.UsedRange.Rows.Count - 2
'Vérifie si le candidat a un statut PMED inclus 6017 MESS
Range("O2:O" & comptCandidats).Formula = "=IF(OR(LEFT(RC[-7],4)=""6017"",LEFT(RC[-3],4)=""6017""),TRUE,IF(ISERROR(AND(RC[-12]=""PMED"",VLOOKUP(RC[-10],'Table PMED'!R2C1:R400C3,3,FALSE)=VLOOKUP(Analyse!R1C20,'Table PMED'!R2C1:R400C3,3,FALSE))),FALSE,AND(RC[-12]=""PMED"",VLOOKUP(RC[-10],'Table PMED'!R2C1:R400C3,3,FALSE)=VLOOKUP(Analyse!R1C20,'Table PMED'!R2C1:R400C3,3,FALSE))))"
'Vérifie si le candidat est dans l'accréditation 12
Range("P2:P" & comptCandidats).Formula = "=R1C16=RC[-9]"
'Vérifie si le candidat est permanent
Range("Q2:Q" & comptCandidats).Formula = "=LEFT(RC[-14],1)=""P"""
'Vérifie si le candidat est auxiliaire
Range("R2:R" & comptCandidats).Formula = "=AND(LEFT(RC[-15],1)=""T"",RC[-15]<>""TETU"")"
'Vérifie si le candidat est employé du service/arrondissement visé à l'affichage. Inclus DC (6004) et FIN(04) ensemble
Range("S2:S" & comptCandidats).Formula = "=OR(LEFT(RC[-11],2)=R1C,AND(RC[-10]=R1C[1],LEFT(RC[-7],2)=R1C),AND(LEFT(RC[-11],2)=""04"",R1C=""60""),AND(RC[-10]=R1C[1],LEFT(RC[-7],2)=""04"",R1C=""60""),AND(LEFT(RC[-11],4)=""6004"",R1C=""04""),AND(RC[-10]=R1C[1],LEFT(RC[-7],4)=""6004"",R1C=""04""))"
'Vérifie si l'emploi de référence du candidat est le même que l'emploi de l'affichage
Range("T2:T" & comptCandidats).Formula = "=R1C20=RC[-15]"
'Vérifie si le candidat est qualifié pour l'emploi visé à l'affichage
Range("U2:U" & comptCandidats).Formula = _
"=IF(ISERROR(VLOOKUP(CONCATENATE(RC[-20],R1C20),ELIG,1,FALSE)),FALSE,TRUE)"
'Vérifie si l'emploi de référence du candidat est un emploi de mut interemplois L3
Range("V2:V" & comptCandidats).Formula = _
"=IF(ISERROR(VLOOKUP(CONCATENATE(Analyse!RC[-17],Analyse!R1C20),Table!R25C5:R47C5,1,FALSE)),FALSE,TRUE)"
Columns("V:V").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Compile l'ensemble des critères auquels répondent chacun des candidats
Range("W2:W" & comptCandidats).Formula = _
"=CONCATENATE(RC[-7],RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
Columns("W:W").EntireColumn.AutoFit
'Analyse si les candidats répondent aux critères des étapes de l'article 19.09 c.c.
Range("X2:X" & comptCandidats).Formula = "=OR(CONCATENATE(RC[-8],RC[-3])=""FAUXVRAI"",AND(RC[-21]=""TETU"",RC[-3]))"
Sheets("Analyse").Select
Range("Y2").Select
Range("Y2:Y" & comptCandidats).Formula = _
"=IF(AND(RC[-10]=TRUE,LEFT(RC[-17],4)=""6017""),""Étape 2 - PMED MESS"",IF(RC[-10]=TRUE,""Étape 2 - PMED du même groupe de traitement"",IF(ISERROR(VLOOKUP(RC[-2],ETAPE2,2,FALSE)),IF(RC[-1]=TRUE,""Étape 8 - Tout autre candidat qualifié"",""N/A""),VLOOKUP(RC[-2],ETAPE2,2,FALSE))))"
Range("Y1").Select
Range("D2").Select
ActiveWindow.FreezePanes = True
Range("D1").Select
'ICI
Range("A1: Y" & comptCandidats).Sort Key1:=Range("Y1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("Y:Y").Select
Selection.ColumnWidth = 22.57
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'ajoute colonne commentaires
Range("Z1").Select
ActiveCell.FormulaR1C1 = "Commentaires"
With ActiveCell.Characters(Start:=1, Length:=12).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
'ajoute le courriel
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Courriel"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("AA2:AA" & comptCandidats).Formula = "=VLOOKUP(RC[-26],base!C[-26]:C[-10],17,FALSE)"
Columns("AA:AA").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("AA:AA").EntireColumn.AutoFit
Application.CutCopyMode = False
'Renomme chacune des feuilles selon leurs noms finaux
Sheets("Base").Select
Sheets("Base").Name = "Original"
Sheets("Admissibilité").Select
Sheets("Admissibilité").Name = "Admiss antérieure"
Sheets("Éligibilité").Select
Sheets("Éligibilité").Name = "Privilège Qual"
'Ajout d'un onglet et import du tableau détaillé de l'article 19.09
Sheets("Analyse").Select
Sheets.Add
ActiveSheet.Name = "Article 19"
Workbooks.Open FileName:=ProcédureEmploi
Windows("ProcédureEmploi.xls").Activate
Cells.Select
Selection.Copy
cl1.Activate
ActiveSheet.Paste
Windows("ProcédureEmploi.xls").Activate
Application.CutCopyMode = False
ActiveWindow.Close
Rows("8:9").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=RIGHT(Original!R[-3]C[-1],LEN(Original!R[-3]C[-1])-39)"
Sheets("Analyse").Select
Range("T1").Select
Selection.Copy
Sheets("Article 19").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R[-1]C[1],'Table PMED'!R[-5]C[-1]:R[393]C,1,FALSE)),"""",VLOOKUP(R[-1]C[1],'Table PMED'!R[-6]C[-1]:R[393]C[1],2,FALSE))"
Range("B8").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A5").Select
'Compte le nombre de candidats répondant à chacune des étapes dans la feuille Analyse
Sheets("Analyse").Select
Range("Y2:Y108").Select
ActiveWorkbook.Names.Add Name:="FINAL", RefersToR1C1:= _
"=Analyse!R2C25:R" & comptCandidats & "C25"
Sheets("Table").Select
Range("M2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(FINAL,RC[-1])"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M19"), Type:=xlFillDefault
Range("M2:M19").Select
Sheets("Article 19").Select
Range("E14").Select
ActiveCell.FormulaR1C1 = "=Table!R[-11]C[8]+Table!R[-10]C[8]"
Range("E15").Select
ActiveCell.FormulaR1C1 = "=Table!R[-9]C[8]"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=Table!R[-8]C[8]"
Range("E17").Select
ActiveCell.FormulaR1C1 = "=Table!R[-6]C[8]+Table!R[-5]C[8]+Table!R[-3]C[8]"
Range("E18").Select
ActiveCell.FormulaR1C1 = "=Table!R[-2]C[8]"
Range("E12").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(FINAL,""Étape 2 - PMED du même groupe de traitement"")+COUNTIF(FINAL,""Étape 2 - PMED MESS"")"
Range("E13").Select
ActiveWindow.SmallScroll Down:=9
Range("E19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(FINAL,""Étape 8 - Tout autre candidat qualifié"")"
Range("E20").Select
'Masque colonne non nécessaire et copie résultat en valeur à la colonne A
Sheets("Analyse").Select
Columns("O:X").Select
Selection.EntireColumn.Hidden = True
Sheets("Privilège Qual").Select
Columns("G:G").Select
Selection.EntireColumn.Hidden = True
Range("A1:F1").Select
Sheets("Analyse").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Columns("Z:Z").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.ColumnWidth = 31.29
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
Selection.AutoFilter
Selection.Font.Bold = True
'Mise en page de la feuille Analyse pour impression
Sheets("Analyse").Select
Columns("B:B").ColumnWidth = 9
Columns("D:D").ColumnWidth = 6.29
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.36)
.RightMargin = Application.InchesToPoints(0.37)
.TopMargin = Application.InchesToPoints(0.53)
.BottomMargin = Application.InchesToPoints(0.51)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
.PrintErrors = xlPrintErrorsDisplayed
End With
'Redimensionner Colonne Commentaire
Columns("AA:AA").Select
Selection.ColumnWidth = 27
'Macro Bordures tableau Analyse - Mous
With Sheets("Analyse")
With .Range("A1").CurrentRegion
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
For i = 1 To .Borders.Count - 2
With .Borders(i)
.LineStyle = xlDot
.Weight = xlThin
End With
Next
End With
With .Range("A1:" & Cells(1, Columns.Count).End(xlToLeft).Address)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ReadingOrder = xlContext
End With
End With
'enleve les formules de l'onglet anlayse (copie valeur)
Sheets("Analyse").Select
Columns("P:Z").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Q6").Select
Application.CutCopyMode = False
'Positionne le fichier à l'onglet Article 19 et fait la mise en page pour la présentation finale
Sheets("Article 19").Select
Range("C6").Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$10"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftMargin = Application.CentimetersToPoints(1)
.RightMargin = Application.CentimetersToPoints(1)
.TopMargin = Application.CentimetersToPoints(0.5)
.BottomMargin = Application.CentimetersToPoints(1)
.HeaderMargin = Application.CentimetersToPoints(0.5)
.FooterMargin = Application.CentimetersToPoints(0)
.PrintTitleRows = ""
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
.Zoom = False
End With
ActiveWindow.DisplayGridlines = False
'Masque les feuilles de travail non nécessaire aux intervenants en dotation
Sheets("Étape Réussie").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Admiss antérieure").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Table").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Table PMED").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Article 19").Select
Range("E11").Select
End Sub</p>
Public Last updated: 2018-04-21 11:43:15 PM
