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""))))))"

 

[Gerald Higgins]

Hi, welcome to the board.

I'm struggling to unravel that formula.

Are you able to get the formula to work by manual entry into the cell ?
If yes, can you post that version of the formula please ?

 

[Joe4]

Welcome to the Board!

That formula looks like a beast!

Since you are using VBA anyway, I would recommend converting it a User Defined Function in VBA.

 

[Triple_M]

Hi, welcome to the board.

I'm struggling to unravel that formula.

Are you able to get the formula to work by manual entry into the cell ?
If yes, can you post that version of the formula please ?

Yes, it works perfectly inside the cell (manually).
The formula as it is in the cell:
=IF(NOT(ISERROR(WEEKNUM('ABC'!$CI3,2))), IF(ISBLANK('ABC'!$AU3),
IF(WEEKNUM('ABC'!$AT3,2)<(WEEKNUM('ABC'!$CI3,2)),
"LATE Released Per Forecast",
"Released Per Forecast"),
IF(WEEKNUM('ABC'!$AU3,2)<(WEEKNUM('ABC'!$CI3,2)),
"LATE Released Per ReForecast",
"Released Per ReForecast")),
IF(OR(ISBLANK('ABC'!$AT3),ISERROR(WEEKNUM('ABC'!$AT3,2)),NOT(ISERROR(SEARCH("Hold",'ABC'!$AV3)))),
"TBD",
IF(ISBLANK('ABC'!$AU3),
IF(WEEKDAY(DEF!$A$1,2)=1,
IF(WEEKNUM('ABC'!$AT3,2)<(WEEKNUM(DEF!$A$1,2)-1),
"LATE DUE",
IF(WEEKNUM('ABC'!$AT3,2)=(WEEKNUM(DEF!$A$1,2)-1),
"Due This Week",
"Future Due")),
IF(WEEKNUM('ABC'!$AT3,2)<(WEEKNUM(DEF!$A$1,2)),
"LATE DUE",
IF(WEEKNUM('ABC'!$AT3,2)=(WEEKNUM(DEF!$A$1,2)),
"Due This Week",
"Future Due"))),
IF(WEEKDAY(DEF!$A$1,2)=1,
IF(WEEKNUM('ABC'!$AU3,2)<(WEEKNUM(DEF!$A$1,2)-1),
"LATE DUE",
IF(WEEKNUM('ABC'!$AU3,2)=(WEEKNUM(DEF!$A$1,2)-1),
"Due This Week",
"Future Due")),
IF(WEEKNUM('ABC'!$AU3,2)<(WEEKNUM(DEF!$A$1,2)),
"LATE DUE",
IF(WEEKNUM('ABC'!$AU3,2)=(WEEKNUM(DEF!$A$1,2)),
"Due This Week",
"Future Due"))))))

 

[Triple_M]

Welcome to the Board!

That formula looks like a beast!

Since you are using VBA anyway, I would recommend converting it a User Defined Function in VBA.

Thanks Joe!
I don't see how a UDF would fix this problem, as I only need to insert this formula into that specific cell as a formula not a value, to be interactive with the related cells.
Can you explain more, probably I don't understand your opinion.

 

[Joe4]

All those things you are using in your formula, like IF, WEEKNUM, ISERROR are built-in Excel functions. You call the function, specify the arguments (inputs), and is returns some value.
In VBA, you can create your own function (a "User Defined Function" or "UDF" for short) to do the same. You can then call/use it like any other built-in Excel function.
The advantage there is:
- it is usually much easier to read/follow in VBA, as it is in a structured format that is more readable that a run-on formula in Excel
- it is usually easier to maintain (if you are using the function in 100 places, and you need to change how it works, you only need to update the VBA code instead of updating a formula in 100 different cells)

Here is a tutorial on creating UDFs: https://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel

 

[MARK858]

I would think that reason you are getting the error is that you are exceeding a 255 character limit and that you need to split it into 2 parts then concatenate it together.

 

[Gerald Higgins]

I'm sure Joe4's suggestion of converting that to a UDF is a very good one, I've never used UDFs so I don't know for sure.

But I think you could possibly also make the formula a bit shorter.

Your original version is over 1,000 characters long. The below is just over 900 characters long, still far too long for VBA as is, as per MARK858's comment, but you can probably do more to make it shorter still.

=IF(NOT(ISERROR(WEEKNUM(ABC!$CI3,2))), IF(ISBLANK(ABC!$AU3),IF(WEEKNUM(ABC!$AT3)<(WEEKNUM(ABC!$CI3)),"LATE ","")&"Released Per Forecast",IF(WEEKNUM(ABC!$AU3)<(WEEKNUM(ABC!$CI3)),"LATE ","")&"Released Per ReForecast"),IF(OR(ISBLANK(ABC!$AT3),ISERROR(WEEKNUM(ABC!$AT3,2)),NOT(ISERROR(SEARCH("Hold",ABC!$AV3)))),"TBD",IF(ISBLANK(ABC!$AU3),IF(WEEKDAY(DEF!$A$1,2)=1,IF(WEEKNUM(ABC!$AT3)<(WEEKNUM(DEF!$A$1)-1),"LATE DUE",IF(WEEKNUM(ABC!$AT3,2)=(WEEKNUM(DEF!$A$1,2)-1),"Due This Week","Future Due")),IF(WEEKNUM(ABC!$AT3)<(WEEKNUM(DEF!$A$1)),"LATE DUE",IF(WEEKNUM(ABC!$AT3)=(WEEKNUM(DEF!$A$1)),"Due This Week","Future Due"))),IF(WEEKDAY(DEF!$A$1,2)=1,IF(WEEKNUM(ABC!$AU3,2)<(WEEKNUM(DEF!$A$1,2)-1),"LATE DUE",IF(WEEKNUM(ABC!$AU3,2)=(WEEKNUM(DEF!$A$1,2)-1),"Due This Week","Future Due")),IF(WEEKNUM(ABC!$AU3)<(WEEKNUM(DEF!$A$1)),"LATE DUE",IF(WEEKNUM(ABC!$AU3)=(WEEKNUM(DEF!$A$1)),"Due This Week","Future Due"))))))

I've only attacked two aspects
1) Changed the way some of the "Released . . . " messages are generated
2) Removed some of the "return type" codes in the WEEKNUM comparisons, I think they are un-necessary for these purposes.

I think there is probably alot more that you might be able to do to make it shorter still.

My personal preference would be to try and avoid doing so much in a single formula, because it's so hard to check that it's giving the right results, or discover what is going wrong if something is wrong - and even to spot that something is actually going wrong at all !
I would look at breaking it out into several helper cells, which can perhaps be hidden if necessary.

 

[Triple_M]

Happy new year everyone
Thanks All for your replies and thanks Gerald for taking the time to summarize that formula

I don't think the number of characters is the issue since I have recorded another formula that is 2935 characters long and it works perfectly.

Besides that, I actually can't use a user-defined function since I need the cells' values to change according to a manual input of dates, hence I need it as a run-on formula.

My goal:
First, I run the VBA code to update the whole file, and put that formula correctly inside the cell.
Second, I then type in dates into some cells (used in my formula).
Result: I need to see if I am Late or not as per the formula.

 

[Joe4]

Besides that, I actually can't use a user-defined function since I need the cells' values to change according to a manual input of dates, hence I need it as a run-on formula.

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