Indirect function to assign dynamically

[rcal01]

I am able to use a formula to calculate the mondays in a month when the user enters a date, example, entering July 10, 2019, states that 3 mondays are left within that month which is correct, but i need to set a range of cells with that formula and i am trying to use VBA to do so, but it gives a 1004 error. I assume its because of the quotation marks "" needed for the INDIRECT function. I have used other formulas such as SUM without any issues.

This if the full formula:
Range("E17:E" & 17 + amount).Formula = "=IF(" & "L17<>0,IF(OR(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(L17&":"&EOMONTH(L17,0))))=2)) > 4,AND(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(L17&":"&EOMONTH(L17,0))))=2))=4,$M$1=4)),((J17*12)/52),0), ((J17*12)/52) )

This is the Portion that calculates the remaining mondays
SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(L17&":"&EOMONTH(L17,0))))=2))

 

[Glove_Man]

My first thought is that you should create a user-defined function to calculated the number of Mondays. If you are using VBA anyway there seems no reason not to...

But if, for some reason, you can't, you can also calculate the number of Mondays using some artithmatic.

Try this formula for the number of MOndays:

=FLOOR.MATH((EOMONTH(L17,0)-L17)/7,1)+IF(MOD(EOMONTH(L17,0)-L17,7)+WEEKDAY(L17,2)>7,1,0)

It's a little longer than yours, but contains no INDIRECT() and is cleaner.

Basically, the logic of it is that the FLOOR.MATH() part calculates the number of full weeks left. Obviously each week has a Monday. The MOD() calculates how many days extra are left after the full weeks. You add that to the day of the week you started on, and if it gets past 7 (i.e. Sunday using WEEKDAY ( ,2)) then you've gone over into the new week and found another Monday.

This approach treats a Monday which is an input as not counting... I.e 29 July 2019 gives 0, becuase 29 July is the last Monday in July.