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
| Private Sub CommandButton1_Click()
Dim LastLig As Long, i As Long, j As Long
Dim Inf() As Variant
LastLig = Cells(Rows.Count, 1).End(xlUp).Row
ReDim Inf(1 To 3, 1 To 1)
j = 1
For i = 9 To LastLig - 1
If Range("A" & i).MergeCells And Range("B" & i + 3).Value <> "" Then
Inf(1, j) = Range("A" & i).Value
Inf(2, j) = i + 3
Inf(3, j) = Range("B" & i + 2).End(xlDown).Row - 1
j = j + 1
ReDim Preserve Inf(1 To 3, 1 To j)
End If
Next i
LastLig = Range("I7").End(xlDown).Row
Range("J7:IV" & LastLig).ClearContents
For j = 1 To UBound(Inf, 2)
Cells(7, j + 9).Value = Inf(1, j)
With Cells(8, j + 9)
.Formula = "=CountIf(" & Range("C" & Inf(2, j) & ":C" & Inf(3, j)).Address & ",I8)"
.AutoFill Range(Cells(8, j + 9), Cells(LastLig, j + 9))
End With
Next j
Cells(7, UBound(Inf, 2) + 9).Value = "TOTAL"
End Sub |
Partager