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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
| ' Macro enregistrée le 29/05/2011 par Levallois
'
Sub Preparation()
Dim Rep As String
Dim NbEq, NbPoint, i, j As Integer
NbEq = InputBox("Nombre d'équipes", "Insérer le nombre d'équipes")
NbPoint = InputBox("Nombre de Points", "Insérer le nombre de points")
If NbEq > 1 Then
With Sheets(2)
.UsedRange.Clear
For i = 1 To NbEq
.Cells(i + 1, 1) = "E" & i
.Cells(1, i + 1) = "E" & i
.Cells(i + 1, i + 1).Interior.ColorIndex = 1
For j = i + 1 To NbEq
cellop = "R[" & (j - i) & "]C[-" & (j - i) & "]"
PtEqOp = NbPoint & "-" & cellop
.Cells(i + 1, j + 1).FormulaR1C1 = "=IF(" & cellop & "<>""""," & PtEqOp & ",""?"")"
Next j
Next i
.Cells(NbEq + 2, 1) = "Total"
.Cells(NbEq + 3, 1) = "nb de parties"
.Cells(NbEq + 4, 1) = "classement"
cellop = "R[-" & NbEq & "]C:R[-1]C"
cellop1 = "R[-" & NbEq & "]C:R[-2]C"
cellop2 = "R[-2]c[0]"
cellop3 = "R[-2]C2:R[-2]C" & NbEq + 1
.Cells(NbEq + 2, 2).FormulaR1C1 = "=sum(" & cellop & ")"
.Cells(NbEq + 3, 2).FormulaR1C1 = "=count(" & cellop1 & ")"
.Cells(NbEq + 4, 2).FormulaR1C1 = "=rank(" & cellop2 & "," & cellop3 & ")"
End With
With Range(Cells(NbEq + 2, 2), Cells(NbEq + 2, NbEq + 1))
.Select
.Interior.ColorIndex = 8
End With
Selection.FormulaR1C1 = "=SUM(" & cellop & ")"
With Range(Cells(NbEq + 3, 2), Cells(NbEq + 3, NbEq + 1))
.Select
.Interior.ColorIndex = 7
End With
Selection.FormulaR1C1 = "=count(" & cellop1 & ")"
With Range(Cells(NbEq + 4, 2), Cells(NbEq + 4, NbEq + 1))
.Select
.Interior.ColorIndex = 5
End With
Selection.FormulaR1C1 = "=rank(" & cellop2 & "," & cellop3 & ")"
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NbEq As Integer, NbPt As Integer
NbEq = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
If NbEq > 1 Then
If Not Intersect(Target, Range(Cells(2, 2), Cells(NbEq, NbEq))) Is Nothing Then
If Target.Count = 1 Then
Application.EnableEvents = False
If Target.Row = Target.Column Or Target.Value > NbPt Then
Target.ClearContents
End If
Application.EnableEvents = True
End If
End If
End If
End Sub |
Partager