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
| Private Sub Worksheet_Change(ByVal Target As Range)
Dim Niv As Byte
Dim Crit As String
If Target.Count = 1 And Target.Row >= 4 Then
Niv = Range("I2").Value
If Target.Column = 1 Then
Crit = "n,i,p,b,t,m"
Crit = Left(Crit, 2 * Niv - 1)
Range("A" & Target.Row & ":I" & Target.Row).Borders.LineStyle = xlContinuous
With Range("B" & Target.Row & ":H" & Target.Row).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Crit
End With
Else
If Target.Column <= 8 And Range("A" & Target.Row) <> "" Then Range("I" & Target.Row) = Sigma(Range("B" & Target.Row & ":H" & Target.Row), Niv)
End If
End If
End Sub
Private Function Sigma(Rng As Range, Niv As Byte) As Double
Dim Coef As Byte
Dim n As Integer
Dim c As Range
Dim Crit As String
Crit = "n,i,p,b,t,m"
Coef = Rng.SpecialCells(xlCellTypeConstants).Count
For Each c In Rng.SpecialCells(xlCellTypeConstants)
n = n + (InStr(Crit, c.Value) - 1) / 2
Next c
Sigma = Application.RoundUp(n * 20 / (Coef * (Niv - 1)), 2)
End Function |
Partager