VBA SUMPRODUCT formula error

[Human_doing]

Hi all,

I have the below formula in cell N2 of a worksheet, can anyone please tell me how to code VBA to add this to each worksheet in a workbook? In this example there are 53 rows but I am looking for the range to be variable. The first two parts of the formula work in isolation (monthsheet is a list of dates, the two parts search for whether the cell value is between two dates), I think it is the third part where I am looking for a match in column D of value 'C22' that is the problem.

=SUMPRODUCT((E2:E53>MonthSheet!B2)*(E2:E53<MonthSheet!C13)*(D2:D53="C22"))

My attempt is below but it triggers Compile error: Expected: end of statement,

Sub Looper()

 'Total the number using formula
    Dim Sh As Worksheet
    Dim LR As Long
    For Each Sh In ActiveWorkbook.Worksheets
    With Sh
  
.Range("N2").Formula = "=SUMPRODUCT((E2:E" & LR & ">MonthSheet!B2)*(E2:E" & LR & "<MonthSheet!C13))*(D2:D" & LR & "=C22"))
               
    End With
     
        
Next Sh
 

End Sub

 

[Trevor G]

Take a look at this thread to see if it helps you

http://www.mrexcel.com/forum/showthread.php?t=65622

 

[Andrew Poulsom]

Try:

.Range("N2").Formula = "=SUMPRODUCT((E2:E" & LR & ">MonthSheet!B2)*(E2:E" & LR & "))"