Блог

Я работаю над проектом VBA, и мне нужен совет. Допустим, у меня есть столбец, заполненный датами, рядом с ним у меня есть таблица с 12 столбцами, где каждый столбец соответствует месяцу года. Я хотел бы понять, как я могу получить месяц в столбце даты и увеличить свою таблицу в соответствии с найденным месяцем. Пример: если дата 01/01/2021, я бы хотел, чтобы ячейка, соответствующая январю, увеличилась на 1. Спасибо

 
Dim donnees As Worksheet
Set donnees = ActiveWorkbook.Worksheets("Données")
Dim result As Worksheet
Set result = ActiveWorkbook.Worksheets("Résultats")
'Compteurs globaux
Dim cpt_total As Integer
Dim cpt_tot_anomalie As Integer
Dim cpt_tot_assistance As Integer
Dim cpt_tot_evolutions As Integer
Dim cpt_tot_autres As Integer
Dim cpt_tot_anciennete As Double
Dim cpt_tot_nbanciennete As Integer
'Variables d'execution
Dim temp_date As Date
Dim temp_int As Integer

cpt_total = 0
cpt_tot_anomalie = 0
cpt_tot_assistance = 0
cpt_tot_evolutions = 0
cpt_tot_autres = 0
cpt_tot_anciennete = 0
cpt_tot_nbanciennete = 0


'vider les cellules du tableau résultats
Worksheets("Résultats").Range("C4:C8").Value = ""
Worksheets("Résultats").Range("E5").Value = ""
Worksheets("Résultats").Range("E13:E19").Value = ""
Worksheets("Résultats").Range("F13:F19").Value = ""
Worksheets("Résultats").Range("G13:G19").Value = ""
Worksheets("Résultats").Range("H13:H19").Value = ""
Set mois = CreateObject("System.Collections.Arraylist")
Dim mois_value(50, 8) As Integer
'recherches des colonnes
Dim strSearch As String

etatSearch = Sheets("Paramètres").Range("S14").Value
resolutionSearch = Sheets("Paramètres").Range("S13").Value
soumissionSearch = Sheets("Paramètres").Range("S11").Value
typeftSearch = Sheets("Paramètres").Range("S10").Value
severiteSearch = Sheets("Paramètres").Range("S9").Value
dateresolutionSearch = Sheets("Paramètres").Range("S12").Value

'trouver à quel numéro de colonne correspond son nom
Set etat = donnees.Rows(1).Find(What:=etatSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set soumission = donnees.Rows(1).Find(What:=soumissionSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set resolution = donnees.Rows(1).Find(What:=resolutionSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Set typeFt = donnees.Rows(1).Find(What:=typeftSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Set severite = donnees.Rows(1).Find(What:=severiteSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

donnees.Activate
nbFt = LastRow(1)
For i = 2 To nbFt Step 1
    If InStr(donnees.Cells(i, etat.Column).Value, "nouveau") > 0 Then
        GoTo NextFT
    End If
    cpt_total = cpt_total   1
   If InStr(donnees.Cells(i, resolution.Column).Value, "ouvert") > 0 Then
        cpt_tot_anciennete = cpt_tot_anciennete   (Now - donnees.Cells(i, soumission.Column).Value)
        cpt_tot_nbanciennete = cpt_tot_nbanciennete   1
    End If
    
    Select Case donnees.Cells(i, typeFt.Column).Value
        Case "Anomalie"
            cpt_tot_anomalie = cpt_tot_anomalie   1
        Case "Demande d'assistance"
            cpt_tot_assistance = cpt_tot_assistance   1
        Case "Evolution"
            cpt_tot_evolutions = cpt_tot_evolutions   1
        Case Else
            cpt_tot_autres = cpt_tot_autres   1
    End Select
    
    temp_date = donnees.Cells(i, soumission.Column).Value
   
   
   
    If Not mois.Contains(CStr(Month(temp_date))) Then
        mois.Add CStr(Month(temp_date))
    End If
    j = mois.IndexOf(CStr(Month(temp_date)), 0)
    Select Case donnees.Cells(i, typeFt.Column).Value
         Case "Anomalie"
            mois_value(j, 4) = mois_value(j, 4)   1
        Case "Demande d'assistance"
            mois_value(j, 5) = mois_value(j, 5)   1
         Case "Evolution"
            mois_value(j, 6) = mois_value(j, 6)   1
        
            End Select
NextFT:
Next
result.Activate
For i = 0 To mois.Count - 1 Step 1
   
    result.Cells(14, 14   i) = mois_value(i, 6)
 result.Cells(15, 14   i).Value = mois_value(i, 5) ' nombre de emandes d'assistances
    result.Cells(16, 14   i).Value = mois_value(i, 4) ' anomalies
   
Next
End Sub