SOMMEPROD(('1'!A:D=A1)*('1'!C:C)) Dans une boucle VBA

Salut à vous

je voudrais bien utiliser SOMMEPROD(('1'!A:D=A1)*('1'!C:C)) Dans une boucle VBA

comme le fichier ci-joint

mes salutations

Bonjour,

A tester :

Sub Test()

Dim DerniereLigne As Long
Dim AireSh2 As Range

  '  With Application
  '       .ScreenUpdating = False
  '       .Calculation = xlCalculationManual
  '  End With

    With Sheets("2")
         DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
         Set AireSh2 = .Range(.Cells(1, 1), .Cells(DerniereLigne, 1))
         With AireSh2.Offset(0, 1)
               .Formula = "=SUMPRODUCT(('1'!$A:$A=A1)*('1'!$C:$C))"
             '  .Copy
             '  .PasteSpecial (xlPasteValues)
         End With
         Set AireSh2 = Nothing

    End With

  '  With Application
  '       .Calculation = xlCalculationAutomatic
  '       .ScreenUpdating = False
  '  End With

End Sub

Bonjour,
Une autre solution.
Cdlt.

Public Sub ConsolidateData()
Dim ws As Worksheet, ws2 As Worksheet, n As Long, sFormula As String

    Set ws = Worksheets("1"): Set ws2 = Worksheets("2")
    n = ws.Cells(Rows.Count, 1).End(xlUp).Row
    sFormula = "('1'!$A$1:$A$" & n & "=A1)*('1'!$C$1:$C$" & n & ")"
    With ws2
        With .Cells(1)
            .CurrentRegion.ClearContents
            .Resize(n) = ws.Cells(1).Resize(n).Value
        End With
        With .Cells(2).Resize(n)
            .Formula = "=SUMPRODUCT(" & sFormula & ")"
            .Value = .Value
        End With
    End With

End Sub

Bonjour à tous !

La demande (solutionnée...) est multi-postée....