Turn Sumproduct Calculation Into vba

**timbo1957** · 09-19-2012, 11:07 AM

Hi,

I have this calcualtion in G4 on a worksheet. Is there a way to get a macro to put this calculation into G4 and copy it to bottom of the data the range of which moves up and down on a monthly basis?

=SUMPRODUCT((Download!I4:I54862="CUSTOMER1")*(Download!H4:H54862=CUSTOMER1!H4)*(Download!G4:G54862))

**timbo1957** · 09-20-2012, 05:50 AM

I have come up with this but it doesn't get around the range problem. i.e. 54858 needs replacing with siommething like lrow.

    ActiveCell.Offset(3, 6).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((Download!RC[2]:R[54858]C[2]=""CUSTOMER1"")*(Download!RC[1]:R[54858]C[1]=CUSTOMER1!RC[1])*(Download!RC:R[54858]C))"
    ActiveCell.Offset(1, 0).Range("A1").Select

**mike7952** · 09-20-2012, 06:01 AM

Try this

Sub t()
Dim lr As Long
 lr = Worksheets("Download").Cells(Rows.Count, "i").End(xlUp).Row
 Range("G4").Resize(Cells(Rows.Count, "h").End(xlUp).Row) _
    .Value = "=SUMPRODUCT((Download!$I$4:$I$" & lr & "=""CUSTOMER1"")*(Download!$H$4:$H" & lr & "=CUSTOMER1!H4)*(Download!$G$4:$G$" & lr & "))"

End Sub