Recorded Macro Gives "1004" Error When Runs

[Triple_M]

I am trying to insert a formula -with so many functions- into a cell using VBA, in order to include it in my bigger code. I wrote the function inside the cell itself and I am using "Record Macro" to get it in the VBA format.
The formula itself works perfectly in Excel, before recording it. When I record it and run this recorded code, it gives "Run-time error '1004': Application-defined or object-defined error", and it doesn't get inserted inside the cell.

About the function: I am using nested IFs to compare between week numbers and weekdays of two cells in the same sheet "ABC" and sometimes with another sheet "DEF". The code is pretty complicated due to the number of functions, but the main problem is how could it be recorded by Excel and then can't run (without any modifications to the recorded code), and if you can see where the error is.
Your help is very appreciated!
Thanks in advance

The recorded code is:
Range("BC2").Select
Selection.FormulaR1C1 = _
"=IF(NOT(ISERROR(WEEKNUM('ABC'!RC87,2)))," & Chr(10) & "IF(ISBLANK('ABC'!RC47)," & Chr(10) & "IF(WEEKNUM('ABC'!RC46,2)<(WEEKNUM('ABC'!RC87,2))," & Chr(10) & """LATE Released Per Forecast""," & Chr(10) & """Released Per Forecast"")," & Chr(10) & "IF(WEEKNUM('ABC'!RC47,2)<(WEEKNUM('ABC'!RC87,2))," & Chr(10) & """LATE Released Per ReForecast""," & Chr(10) & """Released Per ReForecast""))," & Chr(10) & "IF(OR(ISBLANK('ABC'!RC46),ISERROR(WE" & _
"ed-Green'!RC46,2)),NOT(ISERROR(SEARCH(""Hold"",'ABC'!RC48))))," & Chr(10) & """TBD""," & Chr(10) & "IF(ISBLANK('ABC'!RC47)," & Chr(10) & "IF(WEEKDAY(DEF!R1C1,2)=1," & Chr(10) & "IF(WEEKNUM('ABC'!RC46,2)<(WEEKNUM(DEF!R1C1,2)-1)," & Chr(10) & """LATE DUE""," & Chr(10) & "IF(WEEKNUM('ABC'!RC46,2)=(WEEKNUM(DEF!R1C1,2)-1)," & Chr(10) & """Due This Week""," & Chr(10) & """Future Due""))," & Chr(10) & "IF(WEEKNUM('ABC'!RC46,2)<(WEEKNUM(DEF!R1C1,2))," & Chr(10) & """LATE DUE""," & Chr(10) & "IF(" & _
"ABC'!RC46,2)=(WEEKNUM(DEF!R1C1,2))," & Chr(10) & """Due This Week""," & Chr(10) & """Future Due"")))," & Chr(10) & "IF(WEEKDAY(DEF!R1C1,2)=1," & Chr(10) & "IF(WEEKNUM('ABC'!RC47,2)<(WEEKNUM(DEF!R1C1,2)-1)," & Chr(10) & """LATE DUE""," & Chr(10) & "IF(WEEKNUM('ABC'!RC47,2)=(WEEKNUM(DEF!R1C1,2)-1)," & Chr(10) & """Due This Week""," & Chr(10) & """Future Due""))," & Chr(10) & "IF(WEEKNUM('ABC'!RC47,2)<(WEEKNUM(DEF!R1C1,2))," & Chr(10) & """LATE DUE""," & Chr(10) & "IF(WEEKNUM('ABC'!RC47,2)=" & _
"DEF!R1C1,2))," & Chr(10) & """Due This Week""," & Chr(10) & """Future Due""))))))"

 

[Triple_M]

I am not sure what you mean by this. If designed correctly, a UDF should be able to run like any other formula.

What I understand is that the UDF will do the calculations in VBA and return a value that would be stored in each cell. If this is correct, it won't help the purpose I need it for, which is once I change any date manually, it would reflect right away without having to run a VBA code.
If not, please correct me.

Thanks!

 

[Richard U]

I concur with the others. Encapsulate what you want done in a user defined function, then reference it as a formula.

VBA SECTION...

Function myUserFunction(var1,var2,var3)

'insert code here

end function

then reference it as if it were a standard Excel function in the cell

=myUserFunction(1,2,3)

 

[Joe4]

What I understand is that the UDF will do the calculations in VBA and return a value that would be stored in each cell. If this is correct, it won't help the purpose I need it for, which is once I change any date manually, it would reflect right away without having to run a VBA code.
If not, please correct me.

That is not correct. It can be used in VBA OR directly on the sheet, like any other formula you would write in a cell.
Richard U shows an example in the previous post.