1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| Sub Calcul_EngClientOk()
Dim ListeTitre()
Dim ListeParam()
Dim DateDebut As Date
Dim DateFin As Date
Dim Nbl, St As Integer
Dim ShSources As Worksheet
Mt = Month(Date) - 1
DateDebut = "01/01/" & Year(Date)
DateFin = "01/" & Month(Date) & "/" & Year(Date)
Worksheets("tarifé 2015").Activate
Set ShSources = ThisWorkbook.Worksheets("tarifé 2015")
Nbl = Worksheets("tarifé 2015").Range("A1").CurrentRegion.Rows.Count
' on boucle sur chaque colonne cherchée ' et on filtre sur son paramètre dédié
Set c = Nothing
Set c = ShSources.Rows(1).Find("Date de réponse demandée", , xlValues, xlWhole)
If Not c Is Nothing Then ShSources.Range("A1").AutoFilter c.Column, ">=" & DateDebut, xlAnd, "<" & Format(DateFin, "mm/dd/yyyy")
Set e = Nothing
Set e = ShSources.Rows(1).Find("DELAI CLIENT", , xlValues, xlWhole)
If Not e Is Nothing Then ShSources.Range("A1").AutoFilter e.Column, "1"
Set d = Nothing
Set d = ShSources.Rows(1).Find("Nombre", , xlValues, xlWhole)
If Not d Is Nothing Then M = Application.WorksheetFunction.Subtotal(9, Range(Cells(2, d.Column), Cells(Nbl, d.Column)))
ThisWorkbook.Worksheets("eng_client").Range("D2").Value = M
Sheets(5).Range("D2").Value = M
End Sub |
Partager