Hi.

I'm having an issue with an error 1004 application defined or object defined error.

I know the formula works but I've recorded it and something is not right.

I just need to get this bit right for the rest of the script to work.

Can someone please help?


Sub MonthlyReport()
'
' MonthlyReport Macro
'

'

last_row = Cells(Rows.Count, 17).End(xlUp).Row
Range("Y2").Formula = "=IF(RC1=""P2"",RC23+(4/24),IF(RC1=""P1"",RC23+(2/24),LET(a,MOD(RC23,1)<TIME(8,0,0),b,WEEKDAY(RC23,2)<6,c,IF(ISERROR(MATCH(INT(RC23),'Public Holidays'!R1C2:R12C2,0)),TRUE,FALSE),d,LOOKUP(RC1,{""P3"",""P4""},IF(R1C25=""Response Due Date"",{3,10},{6,13})),e,IF(AND(a,b,c,d>1),1,0),f,MOD(RC23,1)>TIME(16,0,0),g,IF(OR(f,OR(a,b=FALSE)),TIME(16,0,0),MOD(RC23,1)+IF(d>1,0,d*8/" & _
"24)),WORKDAY(RC23,IF(d=1,0,d)-e,'Public Holidays'!R1C2:R12C2)+g)))" & _
""
Range("Y2").AutoFill Destination:=Range("Y2:Y" & last_row)

last_row = Cells(Rows.Count, 17).End(xlUp).Row
Range("AC2").Formula = "=IF(RC[-28]=""P2"",RC[-6]+(8/24),IF(RC[-28]=""P1"",RC[-6]+(4/24),LET(a,MOD(RC23,1)<TIME(8,0,0),b,WEEKDAY(RC23,2)<6,c,IF(ISERROR(MATCH(INT(RC23),'Public Holidays'!R1C2:R12C2,0)),TRUE,FALSE),d,LOOKUP(RC1,{""P3"",""P4""},IF(R1C29=""Response Due Date"",{3,10},{6,13})),e,IF(AND(a,b,c,d>1),1,0),f,MOD(RC23,1)>TIME(16,0,0),g,IF(OR(f,OR(a, b=FALSE)),TIME(16,0,0),MOD(RC23,1)+" & _
"IF(d>1,0,d*8/24)),WORKDAY(RC23,IF(d=1,0,d)-e,'Public Holidays'!R1C2:R12C2)+g)))" & _
""
Range("AC2").AutoFill Destination:=Range("AC2:AC" & last_row)

End Sub