Application.WorksheetFunction issue

[RichardMGreen]

Hi all

I've got a worksheet function using SUMPRODUCT that will count all the Mondays in any given month.
It works if I put it on a worksheet quite nicely. The formula is this:-
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH("05/01/2018",-1)+1&":"&EOMONTH("05/01/2018",0))))=2))

You can swap the dates for cell references (which it was originally).

I'm using VBA for something else and I wanted to change the above so I could use Application.WorksheetFunction.SumProduct.... (etc.) but I'm struggling.

I've built the above formula into a variable like this:-
mondays = "=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH(""" & start_date & """,-1)+1&" & """:""" & "&EOMONTH(""" & end_date & """,0))))=2))"

The dates are supplied further up the code.

Now for the difficult part.....

I'm using the WorksheetFunction command like this:-
mondaycount = Application.WorksheetFunction.SumProduct(mondays)
but it gives me an error.

I've tried removing the "=SUMPRODUCT(" bit to give me this:-
mondays = "--(WEEKDAY(ROW(INDIRECT(EOMONTH(""" & start_date & """,-1)+1&" & """:""" & "&EOMONTH(""" & end_date & """,0))))=2))"


but I'm still getting an error.

Can anyone let me know where I'm going wrong please?

::edit::
The error message I get (in both cases) is :-
"Unable to get the SumProduct property of the WorkSheetFunction Class"
with a Run-Time error of 1004

 

[RoryA]

Application.WorksheetFunction.SumProduct requires separate arguments for each part (arrays or ranges) - you can't just pass it a formula string. You could use Evaluate instead, which does expect a formula string.

 

[davesexcel]

If you use the macro recorder, select the formula bar and hit enter, you will then see how the formula looks in vba.

 ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH(""05/01/2018"",-1)+1&"":""&EOMONTH(""05/01/2018"",0))))=2))"

Your code looks to have more quotes than necessary.

When I edit the recorded code

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH(" & start_date & """,-1)+1&"":""&EOMONTH(""" & end_date & """,0))))=2))"

 

[RichardMGreen]

@RoryA - I tried Evaluate but I still got an error (can't remember which one)
@davesexcel - I never though of recording it to be honest

 

[steve the fish]

Heres a couple of ways:

start_date = DateSerial(2018, 1, 5)
For i = Evaluate("EOMONTH(" & CDbl(start_date) & ",-1)+1") To Evaluate("EOMONTH(" & CDbl(start_date) & ",0)")
    If Weekday(i, vbMonday) = 1 Then
        mondays = mondays + 1
    End If
Next

Or maybe:

start_date = DateSerial(2018, 1, 5)
mondays = Evaluate(Replace("INT((EOMONTH(@,0)-EOMONTH(@,-1)+1+WEEKDAY(EOMONTH(@,-1)+1-2))/7)", "@", CDbl(start_date)))

I couldnt get your other formula to work using evaluate. Not sure why.

 

[RichardMGreen]

Thanks Steve
I ended up doing it the loop way (just slightly differently).

The second way works, but I can't for the life of me see why
Can you explain please?

 

[steve the fish]

It is using this formula:

=INT((start-end+WEEKDAY(start-2))/7)

which is readily available with a bit of searching around. Its essentially getting the number of days between each date and then adding/ subtracting days based on the weekday required. It then divides by 7 (days in a week). Finally it produces the integer part of itself. So 4.1 would become 4 for example.