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
| Option Explicit
Function AFTER_REFRESH()
Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, n As Integer
Dim MaPlage As Range
Sheets("fd").Activate
Set MaPlage = Columns("B:B")
i = Application.Match("IC034 - KM PDV / Tour PDV", MaPlage, 0)
j = Application.Match("Kms / Tour Liv Pdv (Régional)", MaPlage, 0)
l = Application.Match("IS201 - Km moteur Tournées PDV", MaPlage, 0)
If Not MaPlage.Find("IS212 - Km à vide Aval") Is Nothing Then
m = Application.Match("IS212 - Km à vide Aval", MaPlage, 0)
Else
m = 0
End If
n = Application.Match("IS184 - Nbre de Tours en liv. PDV", MaPlage, 0)
With Worksheets("fd")
.Range("E" & i).Select
For k = 0 To (j - i - 1)
.Range("E" & (i + k)).FormulaLocal = "=(SOMME.SI.ENS(E:E,$B:$B,$B" & l & ",$C:$C,$C" & (i + k) & ",$A:$A,$A" & (i + k) & ")+SOMME.SI.ENS(E:E,$B:$B,$B" & m & ",$C:$C,$C" & (i + k) & ",$A:$A,$A" & (i + k) & "))/SOMME.SI.ENS(E:E,$B:$B,$B" & n & ",$C:$C,$C" & (i + k) & ",$A:$A,$A" & (i + k) & ")"
Next
.Range("E" & j).Formula = "=(SumIfs(E:E,$B:$B,$B" & l & ",$A:$A,$A" & (i + k) & ")+SumIfs(E:E,$B:$B,$B" & m & ",$A:$A,$A" & (i + k) & "))/SumIfs(E:E,$B:$B,$B" & n & ",$A:$A,$A" & (i + k) & ")"
.Range("E" & i & ":E" & j).Copy
.Range("F" & i & ":G" & j).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
.Range("E" & i & ":G" & j).Copy
.Range("E" & i & ":G" & j).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
AFTER_REFRESH = True
End Function |
