Bonjour,
une proposition, sur base du fichier fourni.
Sub aargh()
Set ws = Sheets("resultats")
dl = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.Cells(4, 1).Resize(dl, 12).Clear
With Sheets("cotation de chaque risque")
ligne = 3
For client = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
For mpp = 2 To .Cells(Rows.Count, 3).End(xlUp).Row
For contrat = 2 To .Cells(Rows.Count, 5).End(xlUp).Row
For distribution = 2 To .Cells(Rows.Count, 7).End(xlUp).Row
For opération = 2 To .Cells(Rows.Count, 9).End(xlUp).Row
ligne = ligne + 1
ws.Cells(ligne, 1) = .Cells(client, 1)
ws.Cells(ligne, 2) = .Cells(client, 2)
ws.Cells(ligne, 3) = .Cells(mpp, 3)
ws.Cells(ligne, 4) = .Cells(mpp, 4)
ws.Cells(ligne, 5) = .Cells(contrat, 5)
ws.Cells(ligne, 6) = .Cells(contrat, 6)
ws.Cells(ligne, 7) = .Cells(distribution, 7)
ws.Cells(ligne, 8) = .Cells(distribution, 8)
ws.Cells(ligne, 9) = .Cells(opération, 9)
ws.Cells(ligne, 10) = .Cells(opération, 10)
ws.Cells(ligne, 11) = (.Cells(client, 2) + .Cells(mpp, 4) + .Cells(contrat, 6) + .Cells(distribution, 8) + .Cells(opération, 10)) / 5
Next opération
Next distribution
Next contrat
Next mpp
Next client
ws.Range("A4").Resize(ligne - 3, 11).Sort key1:=ws.Range("K4"), order1:=xlDescending, key2:=ws.Range("A4"), order2:=xlAscending, Header:=xlNo
End With
End Sub