Recorded macro gets formula error

**CityMPLSEmpolyee** · 02-02-2012, 05:00 PM

I Recorded a Macro and when I test what I Recorded Macro, I get a “run-time error ‘1004’: Application-defined or object-defined error” asking if I want to debug or End, I click debug and it highlights the Active Cell Formula, why

This is the original Formula that was entered in the Cell.
=IF(T21="Yes","show",IF(IF(D21*1>=$D$15,IF(D21*1<=$D$16,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+$R$10))*1>=$E$16,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+$R$10))*1<=$F$16,"show","offset too high"),"offset too low"),"stationing too high"),"stationing too low")="show",IF(S21="Storm","show",IF(S21="Sanitary","Sanitary","not Sewer")),"Don't Show"))

Please Login or Register  to view this content.

**JBeaucaire** · 02-02-2012, 05:32 PM

Try this:

Please Login or Register  to view this content.

**CityMPLSEmpolyee** · 02-06-2012, 04:25 PM

Thanks JBeaucire
but I get the same error

**CityMPLSEmpolyee** · 02-06-2012, 04:32 PM

I would also like to note that in the recoreding of the macro it deletes part of the formula
at the end you should see "not Sewer")),"Don't Show"))
but the recording gets "ewer")),"Don't Show"))

**JBeaucaire** · 02-06-2012, 05:37 PM

Time to post a workbook with verbal description of the logic to evaluate in the order of importance. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

**CityMPLSEmpolyee** · 02-07-2012, 09:39 AM

These are the two functions that are not working for me.
See attached file.

Please Login or Register  to view this content.

I am also work on a macro that will identify if a button has been deleted or not (see if then statement). And the above code will be called into that as will. So note I am still working on other parts of the workbook, but most of it is done.
See macro named "Make_New_Plan_Sheet_Tab", all this you can find in module "Module04_template"

Thanks for your help.

**CityMPLSEmpolyee** · 02-07-2012, 09:48 AM

question, could may formula be to long (is there a limit in macros )?

**JBeaucaire** · 02-07-2012, 02:38 PM

Switch from the FormulaR1C1 syntax to just the Formula syntax and enter the formula into the entire range all at once using the formula for R21 as the formula basis:

Please Login or Register  to view this content.

Wrapping lines of code is meant for your own sanity, it isn't required.

**CityMPLSEmpolyee** · 02-07-2012, 02:57 PM

Thank you very much,
That works great.

Your help and everyone else at this forum are great help.
I don't know what I’d do without it, Thanks

I read the other threads just to get ideas

**JBeaucaire** · 02-07-2012, 03:34 PM

You should get out of the habit of selecting, just send your commands directly to sheets/cells. Here are some example excerpts from your workbook that can be slimmed down as shown:

Please Login or Register  to view this content.

**CityMPLSEmpolyee** · 02-09-2012, 09:55 AM

I applied must of your suggestions, but the original error came back
I’m attaching a test file for your reference. And I am including some sample drainage Report files. I will add the Alignment Clearance Reports in another Reply

Please Login or Register  to view this content.

**CityMPLSEmpolyee** · 02-09-2012, 11:05 AM

The up-loader program for the Forum can’t see the Alignment Clearance Report file as they are created (html format). So I saved them to text files so that you could see the data, but they won’t work as they are, I edited Alignment A so it would be easier to read, the others are as is from the html file. I hope this helps those how are helping or trying to learn from this,