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
| Private Sub Worksheet_Change(ByVal Target As Range)
Dim Nb As String, DerLig As Long, NbVal As Long, i As Long, J As Long
Dim Res As Double
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Address <> "$S$3" Then Exit Sub
Nb = [S3]
If Nb <> "" Then
DerLig = Range("D5").SpecialCells(xlCellTypeLastCell).Row
If DerLig <> 5 Then Range(Cells(6, "S"), Cells(DerLig, "S")).ClearContents
For J = 6 To DerLig
NbVal = 0
Res = 0
For i = 17 To 4 Step -1
If Cells(J, i) <> "" Then
Res = Res + Cells(J, i)
NbVal = NbVal + 1
If NbVal = Nb Then
Cells(J, "S") = Res
Application.EnableEvents = True
Exit For
End If
End If
Next i
Next J
End If
Application.EnableEvents = True
End Sub |
Partager