BsAlv, j'ai modifié mon code en
Application.ScreenUpdating = False
Dim CptNb, ValNb As Range, CalcNb As Range, CptAchat As Range, ValAchat As Range, CalcAchat As Range, CptDividendes As Range, _
ValDividendes As Range, CalcDividendes As Range
Set c = Sheets("Nb actions").Rows(1).Find(Sheets("Récap").Range("W4"), lookat:=xlValue)
Col = c.Column
With Sheets("Nb actions")
Set CptNb = .Range(.Cells(2, 1), .Cells(WorksheetFunction.CountA(.Columns(1)), 1))
Set ValNb = .Range(.Cells(2, 2), .Cells(WorksheetFunction.CountA(.Columns(1)), 2))
Set CalcNb = .Range(.Cells(2, Col), .Cells(WorksheetFunction.CountA(.Columns(1)), Col))
End With
With Sheets("Prix achat")
Set CptAchat = .Range(.Cells(2, 1), .Cells(WorksheetFunction.CountA(.Columns(1)), 1))
Set ValAchat = .Range(.Cells(2, 2), .Cells(WorksheetFunction.CountA(.Columns(1)), 2))
Set CalcAchat = .Range(.Cells(2, Col), .Cells(WorksheetFunction.CountA(.Columns(1)), Col))
End With
With Sheets("Historique dividendes")
Set CptDividendes = .Range(.Cells(2, 1), .Cells(WorksheetFunction.CountA(.Columns(1)), 1))
Set ValDividendes = .Range(.Cells(2, 2), .Cells(WorksheetFunction.CountA(.Columns(1)), 2))
Set CalcDividendes = .Range(.Cells(2, Col), .Cells(WorksheetFunction.CountA(.Columns(1)), Col))
End With
With Sheets("Récap")
For Ligne = 5 To WorksheetFunction.CountA(.Columns(1)) + 1
.Cells(Ligne, 5) = WorksheetFunction.SumIfs(CalcNb, CptNb, .Cells(Ligne, 1), ValNb, .Cells(Ligne, 2), CalcNb, ">0")
.Cells(Ligne, 8) = -WorksheetFunction.SumIfs(CalcNb, CptNb, .Cells(Ligne, 1), ValNb, .Cells(Ligne, 2), CalcNb, "<0")
.Cells(Ligne, 7) = WorksheetFunction.SumIfs(CalcAchat, CptAchat, .Cells(Ligne, 1), ValAchat, .Cells(Ligne, 2), CalcAchat, ">0")
.Cells(Ligne, 10) = -WorksheetFunction.SumIfs(CalcAchat, CptAchat, .Cells(Ligne, 1), ValAchat, .Cells(Ligne, 2), CalcAchat, "<0")
.Cells(Ligne, 21) = WorksheetFunction.SumIfs(CalcDividendes, CptDividendes, .Cells(Ligne, 1), ValDividendes, .Cells(Ligne, 2), CalcDividendes, ">0")
Next Ligne
.Cells(14, 23) = -WorksheetFunction.SumIfs(CalcDividendes, ValDividendes, "0 - Droits de garde", CalcDividendes, "<0")
End With
Application.ScreenUpdating = True
Il est plus complexe à cause de la définition des 9 Range précis à la place de seulement 3 colonnes entières mais ça n'a rien changé sur le temps d'exécution.