VBA SUMPRODUCT with variable range

[Human_doing]

Hi all,

I have the following formula in cell K10 of a worksheet which lets me know how many cells within range E2:311 fall between the dates in cells K2 and K3. However, there will not always be 110 rows below the header row, can anyone please tell me how I could use VBA to calculate the final row with data and add add the appropriate cell reference in place of E111?

=SUMPRODUCT((E2:E111>K2)*(E2:E111 < K3))

Many thanks

 

[Andrew Poulsom]

Her you are:

Sub Test()
    Dim Sh As Worksheet
    Dim LR As Long
    Set Sh = Worksheets("Sheet1")
    With Sh
        LR = .Range("E" & .Rows.Count).End(xlUp).Row
        .Range("K10").Formula = "=SUMPRODUCT((E2:E" & LR & ">K2)*(E2:E" & LR & "<K3))"
    End With
End Sub

 

[Human_doing]

Hi, thanks that is of great help. I wonder if I could ask one more thing - this formula will actually be placed on a huge number of worksheets, could you please tell me how I could create a 'loop' that drops this formula in cell I2 of each worksheet in the workbook?

Thanks

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim LR As Long
    For Each Sh In ActiveWorkbook.Worksheets
        With Sh
            LR = .Range("E" & .Rows.Count).End(xlUp).Row
            .Range("I2").Formula = "=SUMPRODUCT((E2:E" & LR & ">K2)*(E2:E" & LR & "<K3))"
        End With
    Next Sh
End Sub

 

[Human_doing]

Thanks very much for your help!