VBA code using sumproduct and indirect functions

August 3, 2022

I have a sheet containing the following formula in cell F10.
I would like to have a VBA code that will calculate this and place the value in cell

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(F4&":"&F5)),1)=7)*1)

Cell F4 contains a start date and F5 an end date

I tried the following but get a runtime error 438

With ActiveSheet.Range("F10")
        .Value = .Evaluate("SUMPRODUCT((WEEKDAY(ROW(INDIRECT(" & Cells(4, 6) & ":" & Cells(5, 6) & ")),1)=7)*1)")
End With

>Solution :

To fix your specific issue Evaluate is not part of the Range. You need to use the worksheet level instead.

Also it is good to return the .Value2 which will strip the format and return the double.

And you do not need the INDIRECT as you are already using a string

    With ActiveSheet
        Range("F10").Value = .Evaluate("SUMPRODUCT((WEEKDAY(ROW(" & Cells(4, 6).Value2 & ":" & Cells(5, 6).Value2 & "),1)=7)*1)")
    End With