Private Sub CB_type_Change()
'calcul nombre de ligne
Dim total As Integer
nbreligne = 0
I = 2
J = 3
Do While Feuil1.Cells(I, 3) <> ''
nbreligne = nbreligne + 1
I = I + 1
Loop
Range('A3:E' & nbreligne + 1).Clear
'mise en page
Dim mois As String
Dim typeint As String
mois = Mid(TextBox1.Text, 4, 5)
For I = 2 To nbreligne + 1
If Mid(Feuil1.Cells(I, 15), 4, 5) = mois Then
If CB_type.Value = 'Courtier' Then
typeint = 'C'
ElseIf CB_type.Value = 'Agent' Then
typeint = 'A'
Else: typeint = 'S'
End If
If typeint = UCase(Feuil1.Cells(I, 1)) Then
Range('A' & J).FormulaR1C1 = Feuil1.Cells(I, 3)
Range('B' & J).FormulaR1C1 = Feuil1.Cells(I, 14)
Range('C' & J).FormulaR1C1 = Feuil1.Cells(I, 9)
If Feuil1.Cells(I, 19) <> '' Then
Range('D' & J).FormulaR1C1 = 'TPM'
ElseIf Feuil1.Cells(I, 20) <> '' Then
Range('D' & J).FormulaR1C1 = 'TPC'
ElseIf Feuil1.Cells(I, 21) <> '' Then
Range('D' & J).FormulaR1C1 = 'FAC'
ElseIf Feuil1.Cells(I, 22) <> '' Then
Range('D' & J).FormulaR1C1 = '3A'
Else: Range('D' & J).FormulaR1C1 = 'Pas définie'
End If
Range('E' & J).FormulaR1C1 = Feuil1.Cells(I, 23)
J = J + 1
End If
End If
Next I
total = 0
For I = 3 To nbreligne + 1
total = total + Range('E' & I).Value
Next I
Range('E' & J).FormulaR1C1 = 'TOTAL'
Range('E' & J + 1).FormulaR1C1 = total
Dim L As Long
Dim nbTPM, nbTPC, nbFAC, nb3A, totalTPM, totalTPC, totalFAC, total3A As Long
nbTPM = 0
nbTPC = 0
nbFAC = 0
nb3A = 0
totalTPM = 0
totalTPC = 0
totalFAC = 0
total3A = 0
Range('B' & J + 3).FormulaR1C1 = 'Nbr'
Range('C' & J + 4).FormulaR1C1 = 'TPM'
Range('C' & J + 5).FormulaR1C1 = 'TPC'
Range('C' & J + 6).FormulaR1C1 = 'FAC'
Range('C' & J + 7).FormulaR1C1 = '3A'
Range('C' & J + 3).FormulaR1C1 = 'Type'
Range('D' & J + 3).FormulaR1C1 = 'Total'
For L = 3 To J
If Feuil2.Cells(L, 4) = 'TPM' Then
nbTPM = nbTPM + 1
ElseIf Feuil2.Cells(L, 4) = 'TPC' Then
nbTPC = nbTPC + 1
ElseIf Feuil2.Cells(L, 4) = 'FAC' Then
nbFAC = nbFAC + 1
ElseIf Feuil2.Cells(L, 4) = '3A' Then
nb3A = nb3A + 1
End If
If Feuil2.Cells(L, 4) = 'TPM' Then
totalTPM = totalTPM + Feuil2.Cells(L, 5)
ElseIf Feuil2.Cells(L, 4) = 'TPC' Then
totalTPC = totalTPC + Feuil2.Cells(L, 5)
ElseIf Feuil2.Cells(L, 4) = 'FAC' Then
totalFAC = totalFAC + Feuil2.Cells(L, 5)
ElseIf Feuil2.Cells(L, 4) = '3A' Then
total3A = total3A + Feuil2.Cells(L, 5)
End If
Next L
Range('B' & J + 4).FormulaR1C1 = nbTPM
Range('B' & J + 5).FormulaR1C1 = nbTPC
Range('B' & J + 6).FormulaR1C1 = nbFAC
Range('B' & J + 7).FormulaR1C1 = nb3A
Range('D' & J + 4).FormulaR1C1 = totalTPM
Range('D' & J + 5).FormulaR1C1 = totalTPC
Range('D' & J + 6).FormulaR1C1 = totalFAC
Range('D' & J + 7).FormulaR1C1 = total3A
Range('B' & J + 4).Interior.ColorIndex = 3
End Sub
c'est du lourd quand mm!!