VBA code using sumproduct and indirect functions-CodePudding

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

CodePudding user response：

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

To do it solely with vba:

With ActiveSheet
    Dim st As Long
    st = .Cells(4, 6).Value2
    
    Dim ed As Long
    ed = .Cells(5, 6).Value2
    
    Dim cnt As Long
    cnt = 0
    
    Dim i As Long
    For i = st To ed
        If i Mod 7 = 0 Then cnt = cnt   1 '0 = Saturday, 1 = Sunday, and so on.
    Next i
    
    .Cells(10, 6) = cnt
End With