Problème insertion dans une colonne

Le 27/03/2019 à 16:40

Bonjour à tous,

Je dois récupérer des données dans 3 fichiers xls et ensuite les travailler pour en tirer des indicateurs.

Je suis nouveau et je ne sais pas trop comment vous présenter mon problème.

Tout fonctionne bien sauf la partie "catégorie 1 à 5" qui refuse de se copier dans la colonne 26 (Z:Z) de la feuille "Données_Audits".

N'hésitez pas à me poser demander plus d'information, merci d'avance pour votre aide.

Nicolas

MACRO 1 pour récupérer les données des 3 fichiers excel. (La 2ème dans un autre message)

Sub Outil_import()

With Application

.ScreenUpdating = False

.Calculation = xlCalculationManual

.DisplayAlerts = False

End With

'Requête : Indicateurs_devis.sql

'Définition des objets

Dim WbkSource As String ' Nom du fichier Source (divers exports)

Dim WbkDest As String ' Nom de ce fichier (Indicateurs_AC)

Dim WbkLoc As String ' Chemin d'accès aux fichiers

Dim nb_lignes As Long

WbkDest = ThisWorkbook.Name

WbkLoc = ActiveWorkbook.Path

'Affichage des onglets de données

Sheets("Données_devis").Visible = True

Sheets("Données_AC").Visible = True

Sheets("Données_audits").Visible = True

'Devis/contrats

'Ouverture du fichier Source

Workbooks.Open Filename:=WbkLoc & "\Exports\Export_devis.xls"

WbkSource = ActiveWorkbook.Name

'Suppression des données précédentes

Windows(WbkDest).Activate

Sheets("Données_devis").Activate

Cells.ClearContents

Range("A1").Select

'Import des données dans le fichier de traitement

Windows(WbkSource).Activate

Cells.Select

Selection.Copy

Windows(WbkDest).Activate

Sheets("Données_devis").Activate

ActiveSheet.Paste

Windows(WbkSource).Activate

Application.DisplayAlerts = False

Workbooks(WbkSource).Close

Application.DisplayAlerts = True

Windows(WbkDest).Activate

'Mise en forme des données

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

ActiveSheet.Range("$A:$M").RemoveDuplicates Columns:=4, Header:= _

xlYes

Columns("G:G").NumberFormat = "#,##0.00 $"

Cells.Sort Key1:=[A2], Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("A:M").EntireColumn.AutoFit

'Données AC

'Ouverture du fichier Source

Workbooks.Open Filename:="\\10.65.80.3\vol1\_Group\G-Certification\FluidesFrigo\ATT CAPA\Intervenants\Technique\Outils d'étude\Attestation de Capacité\Etude_technique.xls"

WbkSource = ActiveWorkbook.Name

'Suppression des données précédentes

Windows(WbkDest).Activate

Sheets("Données_AC").Activate

Cells.ClearContents

Range("A1").Select

'Import des données dans le fichier de traitement

Windows(WbkSource).Activate

Cells.Select

Selection.Copy

Windows(WbkDest).Activate

Sheets("Données_AC").Activate

ActiveSheet.Paste

Windows(WbkSource).Activate

Application.DisplayAlerts = False

Workbooks(WbkSource).Close

Application.DisplayAlerts = True

Windows(WbkDest).Activate

'Mise en forme des données

Columns("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("M:M").TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("O:O").TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("AB:AB").TextToColumns Destination:=Range("AB1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

'Insertion colonne catégories

Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("U1") = "Catégories"

'Conversion des catégories

nb_lignes = Range("A2").End(xlDown).Row

For i = 2 To nb_lignes

If Cells(i, 22) = "O" Then

Cells(i, 21) = "I"

End If

If Cells(i, 23) = "O" Then

If IsEmpty(Cells(i, 21)) = True Then

Cells(i, 21) = "I-Navire"

Else

Cells(i, 21) = Cells(i, 21) & " I-Navire"

End If

If Cells(i, 24) = "O" Then

If IsEmpty(Cells(i, 21)) = True Then

Cells(i, 21) = "II"

Else

Cells(i, 21) = Cells(i, 21) & " II"

End If

If Cells(i, 25) = "O" Then

If IsEmpty(Cells(i, 21)) = True Then

Cells(i, 21) = "III"

Else

Cells(i, 21) = Cells(i, 21) & " III"

End If

If Cells(i, 26) = "O" Then

If IsEmpty(Cells(i, 21)) = True Then

Cells(i, 21) = "IV"

Else

Cells(i, 21) = Cells(i, 21) & " IV"

End If

If Cells(i, 27) = "O" Then

If IsEmpty(Cells(i, 21)) = True Then

Cells(i, 21) = "V"

Else

Cells(i, 21) = Cells(i, 21) & " V"

End If

If Cells(i, 28) = "O" Then

If IsEmpty(Cells(i, 21)) = True Then

Cells(i, 21) = "V-VHU"

Else

Cells(i, 21) = Cells(i, 21) & " V-VHU"

End If

Columns("V:AB").EntireColumn.Delete Shift:=xlToLeft

Range("A1").Select

'Tri par date de fin d'AC

Cells.Sort Key1:=[D2], Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'Récupération des infos de catégorie au niveau des devis

Sheets("Données_devis").Activate

Range("N1") = "Catégorie"

nb_lignes = Range("A2").End(xlDown).Row

For i = 2 To nb_lignes

If Cells(i, 2) = "Contrat" Then

Cells(i, 14) = Application.VLookup(Cells(i, 4), Sheets("Données_AC").Columns("E:U"), 17, False)

End If

'Données Audits

'Ouverture du fichier Source

Workbooks.Open Filename:="\\10.65.80.3\vol1\_Group\G-Certification\FluidesFrigo\ATT CAPA\Intervenants\Technique\Outils d'étude\Audits\Export_audits.xls"

WbkSource = ActiveWorkbook.Name

'Suppression des données précédentes

Windows(WbkDest).Activate

Sheets("Données_Audits").Activate

Cells.ClearContents

Range("A1").Select

'Import des données dans le fichier de traitement

Windows(WbkSource).Activate

Cells.Select

Selection.Copy

Windows(WbkDest).Activate

Sheets("Données_Audits").Activate

ActiveSheet.Paste

Windows(WbkSource).Activate

Application.DisplayAlerts = False

Workbooks(WbkSource).Close

Application.DisplayAlerts = True

Windows(WbkDest).Activate

'Mise en forme des données

'Conversion des dates et numéro SGO

Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("Q:Q").TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 4), TrailingMinusNumbers:=True

Columns("X:X").TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True

'Insertion colonne catégories et type auditeur

Columns("Z:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("Z1") = "Catégories"

Range("AH1") = "Type_auditeur"

'Conversion des catégories et récupération des infos auditeur

nb_lignes = Range("A2").End(xlDown).Row

For i = 2 To nb_lignes

If Cells(i, 27) = "O" Then

Cells(i, 26) = "I"

End If

If Cells(i, 28) = "O" Then

If IsEmpty(Cells(i, 26)) = True Then

Cells(i, 26) = "I-Navire"

Else

Cells(i, 26) = Cells(i, 26) & " I-Navire"

End If

If Cells(i, 29) = "O" Then

If IsEmpty(Cells(i, 26)) = True Then

Cells(i, 26) = "II"

Else

Cells(i, 26) = Cells(i, 26) & " II"

End If

If Cells(i, 30) = "O" Then

If IsEmpty(Cells(i, 26)) = True Then

Cells(i, 26) = "III"

Else

Cells(i, 26) = Cells(i, 26) & " III"

End If

If Cells(i, 31) = "O" Then

If IsEmpty(Cells(i, 26)) = True Then

Cells(i, 26) = "IV"

Else

Cells(i, 26) = Cells(i, 26) & " IV"

End If

If Cells(i, 32) = "O" Then

If IsEmpty(Cells(i, 26)) = True Then

Cells(i, 26) = "V"

Else

Cells(i, 26) = Cells(i, 26) & " V"

End If

If Cells(i, 33) = "O" Then

If IsEmpty(Cells(i, 26)) = True Then

Cells(i, 26) = "V-VHU"

Else

Cells(i, 26) = Cells(i, 26) & " V-VHU"

End If

If IsEmpty(Cells(i, 5)) = False Then

Cells(i, 34) = Application.VLookup(Cells(i, 5), Sheets("Auditeurs").Columns("A:B"), 2, False)

End If

Columns("AA:AG").EntireColumn.Delete Shift:=xlToLeft

Range("A1").Select

'MsgBox

Sheets("Outil").Select

MsgBox "Import des données réussi", vbOKOnly + vbInformation

With Application

.ScreenUpdating = True

.Calculation = xlCalculationAutomatic

.DisplayAlerts = True

End With

End Sub

Le 27/03/2019 à 16:41

MACRO 2 pour analysez les fichiers

'Déclaration variables

Dim DateRef As Date

Dim DateSup As Date

'Devis

Dim DateDevis As Range

Dim TypeDevis As Range

Dim StatutDevis As Range

Dim DateContrat As Range

Dim Montant As Range

Dim Nb_ech As Range

Dim Nb_etab As Range

Dim CGV_2013 As Range

Dim Ct_renew As Range

Dim Sign_web As Range

Dim CategoryCT As Range

'AC

Dim Num_AC As Range

Dim Statut_AC As Range

Dim Date_debut_AC As Range

Dim Date_fin_AC As Range

Dim StatutContratAC As Range

Dim DateContratAC As Range

Dim ACRenew As Range

Dim StatutEtab As Range

Dim StatutCollab As Range

Dim StatutOut As Range

Dim StatutDA As Range

Dim CategoryAC As Range

Dim ComAC As Range

'Audits

Dim TypeAudit As Range

Dim StatutAudit As Range

Dim DateAudit As Range

Dim Auditeur As Range

Dim StatutRapport As Range

Dim AuditComp As Range

Dim CategoryAudit As Range

Dim TypeAuditeur As Range

Sub Indicateurs_AC()

With Application

.ScreenUpdating = False

.Calculation = xlCalculationManual

.DisplayAlerts = False

End With

'Affichage des onglets de données

Sheets("Données_devis").Visible = True

Sheets("Données_AC").Visible = True

Sheets("Données_audits").Visible = True

'Initialisation des variables

'AC

Set Num_AC = Sheets("Données_AC").Columns(1)

Set Statut_AC = Sheets("Données_AC").Columns(2)

Set StatutContratAC = Sheets("Données_AC").Columns(6)

'Audits

Set TypeAudit = Sheets("Données_Audits").Columns(2)

Set StatutAudit = Sheets("Données_Audits").Columns(3)

'Indicateurs mensuels

Call Indic_AC.Indic_mensuels

DoEvents

'Indicateurs hebdo

Call Indic_AC.Indic_hebdo

DoEvents

'Détail par auditeurs

MAJ_Auditeurs = MsgBox("Souhaitez-vous mettre à jour le détail par auditeurs ?", vbYesNo + vbQuestion)

If MAJ_Auditeurs = 6 Then

Call Indic_AC.Detail_auditeurs

DoEvents

End If

'Masque des onglets de données

Sheets("Données_devis").Visible = False

Sheets("Données_AC").Visible = False

Sheets("Données_audits").Visible = False

'MsgBox + synthèse

Sheets("Outil").Activate

Range("E22") = Application.CountIf(Statut_AC, "Valide")

Range("E23") = Application.CountIf(Statut_AC, "Suspendue")

Range("E24") = Application.CountIfs(Statut_AC, "En attente d information", StatutContratAC, "En cours") + _

Application.CountIfs(Statut_AC, "En attente d information", StatutContratAC, "En attente de règlement") + _

Application.CountIfs(Statut_AC, "En attente d information", StatutContratAC, "Incomplet") + _

Application.CountIfs(Statut_AC, "En cours d étude", StatutContratAC, "En cours") + _

Application.CountIfs(Statut_AC, "En cours d étude", StatutContratAC, "En attente de règlement") + _

Application.CountIfs(Statut_AC, "En cours d étude", StatutContratAC, "Incomplet")

Range("E25") = Range("E22") + Range("E23") + Range("E24")

Range("E26") = Application.CountIf(Num_AC, "*- R1")

Range("E27") = Application.CountIf(Statut_AC, "Echue")

Range("E28") = Application.CountIf(Statut_AC, "Retirée") + Application.CountIf(Statut_AC, "Remplacée")

Range("J22") = Application.CountIfs(TypeAudit, "Principal", StatutAudit, "CONC") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "NCMN") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "NCMJ") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "ATWT") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "SUBM") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "NONC")

Range("J23") = Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "CONC") + _

Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "NCMN") + _

Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "NCMJ") + _

Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "ATWT") + _

Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "SUBM") + _

Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "NONC")

Range("J24") = Range("J22") + Range("J23")

Range("J25") = Application.CountIfs(TypeAudit, "Principal", StatutAudit, "TOPL")

Range("J26") = Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "TOPL")

Range("J27") = Range("J25") + Range("J26")

Range("J28") = Application.CountIf(StatutAudit, "PLAN")

Range("D30") = Date & " à " & Time

MsgBox "Indicateurs actualisés", vbOKOnly + vbInformation

With Application

.ScreenUpdating = True

.Calculation = xlCalculationAutomatic

.DisplayAlerts = True

End With

End Sub

Sub Indic_mensuels()

With Application

.ScreenUpdating = False

.Calculation = xlCalculationManual

.DisplayAlerts = False

End With

Sheets("Indicateurs_mensuels").Activate

'Initialisation des variables

'Devis

Set DateDevis = Sheets("Données_devis").Columns(1)

Set TypeDevis = Sheets("Données_devis").Columns(2)

Set StatutDevis = Sheets("Données_devis").Columns(3)

Set DateContrat = Sheets("Données_devis").Columns(5)

Set Montant = Sheets("Données_devis").Columns(7)

Set Nb_ech = Sheets("Données_devis").Columns(8)

Set Nb_etab = Sheets("Données_devis").Columns(9)

Set CGV_2013 = Sheets("Données_devis").Columns(10)

Set Ct_renew = Sheets("Données_devis").Columns(12)

Set Sign_web = Sheets("Données_devis").Columns(13)

Set CategoryCT = Sheets("Données_devis").Columns(14)

'AC

Set Num_AC = Sheets("Données_AC").Columns(1)

Set Statut_AC = Sheets("Données_AC").Columns(2)

Set Date_debut_AC = Sheets("Données_AC").Columns(3)

Set Date_fin_AC = Sheets("Données_AC").Columns(4)

Set DateContratAC = Sheets("Données_AC").Columns(7)

Set ACRenew = Sheets("Données_AC").Columns(8)

Set StatutEtab = Sheets("Données_AC").Columns(9)

Set StatutCollab = Sheets("Données_AC").Columns(10)

Set StatutOut = Sheets("Données_AC").Columns(11)

Set StatutDA = Sheets("Données_AC").Columns(14)

Set CategoryAC = Sheets("Données_AC").Columns(21)

Set ComAC = Sheets("Données_AC").Columns(24)

'Audits

Set TypeAudit = Sheets("Données_Audits").Columns(2)

Set StatutAudit = Sheets("Données_Audits").Columns(3)

Set DateAudit = Sheets("Données_Audits").Columns(4)

Set Auditeur = Sheets("Données_Audits").Columns(5)

Set StatutRapport = Sheets("Données_Audits").Columns(7)

Set AuditComp = Sheets("Données_Audits").Columns(11)

Set CategoryAudit = Sheets("Données_Audits").Columns(26)

Set TypeAuditeur = Sheets("Données_Audits").Columns(27)

'Boucle par mois

For i = 34 To 86 'numéro de lignes - début à la ligne 34 car màj 2013/2014/début 2015 inutile

DateRef = Cells(i, 1)

DateSup = DateAdd("m", 1, DateRef)

'Devis manuel en cours

Cells(i, 2) = Application.CountIfs(TypeDevis, "Devis manuel", StatutDevis, "En cours", DateDevis, ">=" _

& CDbl(CDate(DateRef)), DateDevis, "<" & CDbl(CDate(DateSup)))

'Devis automatique en cours

Cells(i, 3) = Application.CountIfs(TypeDevis, "Devis automatique", StatutDevis, "En cours", DateDevis, ">=" _

& CDbl(CDate(DateRef)), DateDevis, "<" & CDbl(CDate(DateSup)))

'Devis incomplet (manuel ou auto)

Cells(i, 4) = Application.CountIfs(TypeDevis, "Devis*", StatutDevis, "Incomplet", DateDevis, ">=" _

& CDbl(CDate(DateRef)), DateDevis, "<" & CDbl(CDate(DateSup)))

'Devis annulé (manuel ou auto)

Cells(i, 5) = Application.CountIfs(TypeDevis, "Devis*", StatutDevis, "Annulé", DateDevis, ">=" _

& CDbl(CDate(DateRef)), DateDevis, "<" & CDbl(CDate(DateSup)))

'Passés en contrat

Cells(i, 6) = Application.CountIfs(TypeDevis, "Contrat", DateDevis, ">=" & CDbl(CDate(DateRef)), DateDevis, "<" _

& CDbl(CDate(DateSup)))

'Total de devis

Cells(i, 7) = Application.CountIfs(DateDevis, ">=" & CDbl(CDate(DateRef)), DateDevis, "<" & CDbl(CDate(DateSup)))

'Montant de devis

Cells(i, 8) = Application.SumIfs(Montant, DateDevis, ">=" & CDbl(CDate(DateRef)), DateDevis, "<" & CDbl(CDate(DateSup)))

'1 échéance

Cells(i, 9) = Application.CountIfs(TypeDevis, "Contrat", DateContrat, ">=" & CDbl(CDate(DateRef)), DateContrat, "<" _

& CDbl(CDate(DateSup)), Nb_ech, "1")

'2 échéances

Cells(i, 10) = Application.CountIfs(TypeDevis, "Contrat", DateContrat, ">=" & CDbl(CDate(DateRef)), DateContrat, "<" _

& CDbl(CDate(DateSup)), Nb_ech, "2")

'5 échéances

Cells(i, 11) = Application.CountIfs(TypeDevis, "Contrat", DateContrat, ">=" & CDbl(CDate(DateRef)), DateContrat, "<" _

& CDbl(CDate(DateSup)), Nb_ech, "5")

'Total

Cells(i, 12) = Application.CountIfs(TypeDevis, "Contrat", DateContrat, ">=" & CDbl(CDate(DateRef)), DateContrat, "<" _

& CDbl(CDate(DateSup)))

'Montant contrats

Cells(i, 13) = Application.SumIfs(Montant, TypeDevis, "Contrat", DateContrat, ">=" & CDbl(CDate(DateRef)), DateContrat, "<" _

& CDbl(CDate(DateSup)))

'CGV_2013

Cells(i, 14) = Application.CountIfs(TypeDevis, "Contrat", DateContrat, ">=" & CDbl(CDate(DateRef)), DateContrat, "<" _

& CDbl(CDate(DateSup)), CGV_2013, "1")

'Signature web

Cells(i, 15) = Application.CountIfs(TypeDevis, "Contrat", DateContrat, ">=" & CDbl(CDate(DateRef)), DateContrat, "<" _

& CDbl(CDate(DateSup)), Sign_web, ">" & "0")

'Nouveaux etab signés - cat. I

Cells(i, 16) = Application.CountIfs(Num_AC, "<>" & "*- R1", CategoryAC, "*I*", DateContratAC, ">=" _

& CDbl(CDate(DateRef)), DateContratAC, "<" & CDbl(CDate(DateSup)))

'Nouveaux etab signés - cat. V

Cells(i, 17) = Application.CountIfs(Num_AC, "<>" & "*- R1", CategoryAC, "V*", DateContratAC, ">=" _

& CDbl(CDate(DateRef)), DateContratAC, "<" & CDbl(CDate(DateSup)))

'Nouveaux etab signés

Cells(i, 18) = Application.CountIfs(Num_AC, "<>" & "*- R1", DateContratAC, ">=" & CDbl(CDate(DateRef)), DateContratAC, "<" _

& CDbl(CDate(DateSup)))

'Renouvellements etab signés - cat. I

Cells(i, 19) = Application.CountIfs(Num_AC, "*- R1", CategoryAC, "*I*", DateContratAC, ">=" _

& CDbl(CDate(DateRef)), DateContratAC, "<" & CDbl(CDate(DateSup)))

'Renouvellements etab signés - cat. V

Cells(i, 20) = Application.CountIfs(Num_AC, "*- R1", CategoryAC, "V*", DateContratAC, ">=" _

& CDbl(CDate(DateRef)), DateContratAC, "<" & CDbl(CDate(DateSup)))

'Renouvellements etab signés

Cells(i, 21) = Application.CountIfs(Num_AC, "*- R1", DateContratAC, ">=" & CDbl(CDate(DateRef)), DateContratAC, "<" _

& CDbl(CDate(DateSup)))

'Moyenne montant étab - cat. I

If Application.SumIfs(Nb_etab, TypeDevis, "Contrat", CategoryCT, "*I*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup))) <> "0" Then

Cells(i, 22) = Application.SumIfs(Montant, TypeDevis, "Contrat", CategoryCT, "*I*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup))) / _

Application.SumIfs(Nb_etab, TypeDevis, "Contrat", CategoryCT, "*I*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup)))

Else

Cells(i, 22) = 0

End If

'Moyenne montant étab - cat. V

If Application.SumIfs(Nb_etab, TypeDevis, "Contrat", CategoryCT, "V*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup))) <> "0" Then

Cells(i, 23) = Application.SumIfs(Montant, TypeDevis, "Contrat", CategoryCT, "V*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup))) / _

Application.SumIfs(Nb_etab, TypeDevis, "Contrat", CategoryCT, "V*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup)))

Else

Cells(i, 23) = 0

End If

'Moyenne montant étab

If (Cells(i, 22) + Cells(i, 23)) <> "0" Then

Cells(i, 24) = (Application.SumIfs(Montant, TypeDevis, "Contrat", CategoryCT, "*I*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup))) + _

Application.SumIfs(Montant, TypeDevis, "Contrat", CategoryCT, "V*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup)))) / _

(Application.SumIfs(Nb_etab, TypeDevis, "Contrat", CategoryCT, "*I*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup))) + _

Application.SumIfs(Nb_etab, TypeDevis, "Contrat", CategoryCT, "V*", DateContrat, ">=" _

& CDbl(CDate(DateRef)), DateContrat, "<" & CDbl(CDate(DateSup))))

Else

Cells(i, 24) = 0

End If

'AC à échéance

Cells(i, 25) = Application.CountIfs(Statut_AC, "Valide", Date_fin_AC, ">=" & CDbl(CDate(DateRef)), _

Date_fin_AC, "<" & CDbl(CDate(DateSup))) + Application.CountIfs(Statut_AC, "Suspendue", Date_fin_AC, ">=" & _

CDbl(CDate(DateRef)), Date_fin_AC, "<" & CDbl(CDate(DateSup))) + Application.CountIfs(Statut_AC, "Echue", _

Date_fin_AC, ">=" & CDbl(CDate(DateRef)), Date_fin_AC, "<" & CDbl(CDate(DateSup)))

'AC renouvelées

If Cells(i, 25) <> 0 Then

Cells(i, 26) = Application.CountIfs(Statut_AC, "Valide", Date_fin_AC, ">=" & CDbl(CDate(DateRef)), _

Date_fin_AC, "<" & CDbl(CDate(DateSup)), ACRenew, "Oui") + Application.CountIfs(Statut_AC, "Suspendue", _

Date_fin_AC, ">=" & CDbl(CDate(DateRef)), Date_fin_AC, "<" & CDbl(CDate(DateSup)), ACRenew, "Oui") _

+ Application.CountIfs(Statut_AC, "Echue", Date_fin_AC, ">=" & CDbl(CDate(DateRef)), Date_fin_AC, "<" & _

CDbl(CDate(DateSup)), ACRenew, "Oui")

Else

Cells(i, 26) = 0

End If

'Taux de renouvellement

If Cells(i, 25) <> 0 Then

Cells(i, 27) = Cells(i, 26) / Cells(i, 25)

Else

Cells(i, 27) = 0

End If

'Pré-études

If Cells(i, 26) <> 0 Then

Cells(i, 28) = Application.CountIfs(Statut_AC, "Valide", Date_fin_AC, ">=" & CDbl(CDate(DateRef)), Date_fin_AC, "<" _

& CDbl(CDate(DateSup)), ACRenew, "Oui", ComAC, "*tude renouvellement*") + _

Application.CountIfs(Statut_AC, "Suspendue", Date_fin_AC, ">=" & CDbl(CDate(DateRef)), Date_fin_AC, "<" _

& CDbl(CDate(DateSup)), ACRenew, "Oui", ComAC, "*tude renouvellement*") + _

Application.CountIfs(Statut_AC, "Echue", Date_fin_AC, ">=" & CDbl(CDate(DateRef)), Date_fin_AC, "<" _

& CDbl(CDate(DateSup)), ACRenew, "Oui", ComAC, "*tude renouvellement*")

Else

Cells(i, 28) = 0

End If

'Taux de pré-études

If Cells(i, 26) <> 0 Then

Cells(i, 29) = Cells(i, 28) / Cells(i, 26)

Else

Cells(i, 29) = 0

End If

'Nouvelles AC délivrées

Cells(i, 30) = Application.CountIfs(Num_AC, "<>" & "*- R1", Date_debut_AC, ">=" _

& CDbl(CDate(DateRef)), Date_debut_AC, "<" & CDbl(CDate(DateSup)))

'AC renouvelées délivrées

Cells(i, 31) = Application.CountIfs(Num_AC, "*- R1", Date_debut_AC, ">=" _

& CDbl(CDate(DateRef)), Date_debut_AC, "<" & CDbl(CDate(DateSup)))

'Audits initiaux planifiés cat. I

Cells(i, 32) = Application.CountIfs(TypeAudit, "Principal", StatutAudit, "PLAN", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*")

'Audits initiaux planifiés cat. V

Cells(i, 33) = Application.CountIfs(TypeAudit, "Principal", StatutAudit, "PLAN", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "V*")

'Audits comp. planifiés cat. I

Cells(i, 34) = Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "PLAN", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*")

'Audits comp. planifiés cat. V

Cells(i, 35) = Application.CountIfs(TypeAudit, "Complémentaire", StatutAudit, "PLAN", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "V*")

'Total planifiés

Cells(i, 36) = Cells(i, 32) + Cells(i, 33) + Cells(i, 34) + Cells(i, 35)

'Initiaux réalisés int. cat. I

Cells(i, 37) = Application.CountIfs(TypeAudit, "Principal", StatutAudit, "CONC", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*", TypeAuditeur, "Interne") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "NCMN", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*", TypeAuditeur, "Interne") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "NCMJ", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*", TypeAuditeur, "Interne") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "ATWT", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*", TypeAuditeur, "Interne") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "SUBM", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*", TypeAuditeur, "Interne") + _

Application.CountIfs(TypeAudit, "Principal", StatutAudit, "NONC", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*", TypeAuditeur, "Interne")

'Initiaux réalisés ST cat. I

Cells(i, 38) = Application.CountIfs(TypeAudit, "Principal", StatutAudit, "CONC", DateAudit, ">=" _

& CDbl(CDate(DateRef)), DateAudit, "<" & CDbl(CDate(DateSup)), CategoryAudit, "*I*", TypeAuditeur, "ST") + _