Unable to incorporate a recorded code in a bigger code

[VKS]

Hi,

I am facing a problem with incorporating a recorded code in another code.

When I paste the recorded code in my main code I get run time error 1004.

Recorder gave me the code below

Please Login or Register  to view this content.

My code looks like :

Please Login or Register  to view this content.

Please help.
VKS

[joe31623]

I believe you are getting the error because the cells would contain a character string that is more than 911 characters upon execution.

You can take all those If statements and implement them in VBA or distribute some of that logic among multiple cells so that no cell will contain more than 911 characters. I would choose the former solution because the logic will be easier to understand, debug, and extend to other parts of your project.

[VKS]

I believe you are getting the error because the cells would contain a character string that is more than 911 characters upon execution.

You can take all those If statements and implement them in VBA or distribute some of that logic among multiple cells so that no cell will contain more than 911 characters. I would choose the former solution because the logic will be easier to understand, debug, and extend to other parts of your project.

Hello Joe,

Thanks for your comments. Could you please elaborate a bit more on "implement them in VBA"
BTW =Len(column) suggests I have 434 characters only

Thanks/VKS

[joe31623]

Interesting... the 911 limit may be associated with excel versions later than 2007.

I would expect that you would be interested in a solution that does not require a cell with a formula 434 characters long. That could be implemented in VBA much more clearly.

For example, looking at a very small subset of your logic, I see: IF(AND(OR(LEFT(RC[-31],3)... that seems to go on forever. I'll simplify that a little bit and close up that logic as simply as possible by replacing "RC[-31] by a cell value in order to illustrate my point.

Let's say you had: = IF(AND(OR(LEFT(A1,3)= "MAR", A1 = "MARCH"),OR(LEFT(A2,3) = "FEB", A2 = "FEBRUARY"),"Cell A1 is MARCH/MAR & Cell A2 is FEBRUARY/FEB","") typed in Cell A3, it would either display: "Cell A1 is MARCH/MAR & Cell A2 is FEBRUARY/FEB" or nothing... but that is difficult to understand from looking at that monster formula.
Alternatively, if you wrote a VBA implementation that does the same thing:

Please Login or Register  to view this content.

...it is much easier to debug. So if there is an error, I could step through the code line by line, and use debugging tools to do things like return the value of A2 while the code is running to see what it is and why you're not getting the expected result.

Recording a macro can be an easy way to get started coding but for reasons you're experiencing, it can lead to lots of issues if you don't know what the code does. And no one wants to debug a cell that is "only" 434 characters long because you get lost in parenthesis and embedded if/and/or... statements.

So. If you describe what you want to do or record a series of more simple macros or upload a worksheet for the forum to debug/look at which includes your raw data on one worksheet and desired results on another worksheet, you may be able to move forward on your project more easily.