Using Sumproduct to last row

[deduwa]

Hi,

I am trying to get the sumproduct formula down to last row using vba. The following code returns a compile error.

Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row


Range("AE1").Select
ActiveCell.Formula = "=SUMPRODUCT((S2:S"&lastRow&")&","&(Z2:Z"&lastRow&"))/sum(s2:s"&lastRow&")"

Basically in Cell AE1 the resulting formula should look like the below (Assuming last Row is 150);

=SUMPRODUCT(S2:S150,Z2:Z150)/SUM(S2:S150)

Any help would be appreciated.

Thanks

 

[RoryA]

It should read:

Range("AE1").Formula = "=SUMPRODUCT(S2:S" & lastRow & ",Z2:Z" & lastRow & ")/sum(s2:s" & lastRow & ")"

 

[deduwa]

Thank you Rory

 

[Kamolga]

You have extra () and don't need to select the cell

Sub test()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("AE1").Formula = "=SUMPRODUCT(S2:S" & lr & ",Z2:Z" & lr & ")/sum(s2:s" & lr & ")"
End Sub

Note that last row is calculated on A, not S not Z (could get an error)