Recorded macro gets formula error

**CityMPLSEmpolyee** · 02-02-2012, 04: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"))


Sub Macro24()
'
' Macro24 Macro
'

'
    Range("R21").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[2]=""Yes"",""show"",IF(IF(RC[-14]*1>=R15C4,IF(RC[-14]*1<=R16C4,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+R10C18))*1>=R16C5,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+R10C18))*1<=R16C6,""show"",""offset too high""),""offset too low""),""stationing too high""),""stationing too low"")=""show"",IF(RC[1]=""Storm"",""show"",IF(RC[1]=""Sanitary"",""Sanitary""" & _
        "ewer"")),""Don't Show""))"
    Range("R21").Select
    Selection.Copy
    Range("R22").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.Goto Reference:="Print_Area"
End Sub

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

Try this:

Sub AddFormula()
Dim LR As Long

LR = Range("R" & Rows.Count).End(xlUp).Row

Range("R21:R" & LR).FormulaR1C1 = "=IF(RC[2]=""Yes"",""show"",IF(IF(RC[-14]*1>=R15C4,IF(RC[-14]*1<=R16C4,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+R10C18))*1>=R16C5,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+R10C18))*1<=R16C6,""show"",""offset too high""),""offset too low""),""stationing too high""),""stationing too low"")=""show"",IF(RC[1]=""Storm"",""show"",IF(RC[1]=""Sanitary"",""Sanitary""" & "ewer"")),""Don't Show""))"
    
Application.Goto Reference:="Print_Area"
End Sub

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

Thanks JBeaucire
but I get the same error

**CityMPLSEmpolyee** · 02-06-2012, 03: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, 04: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, 08:39 AM

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


Sub Filter_Sanitary()
                MsgBox "Add Sanitary Formula at Range R21:R420"
    Range("R21:R420").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[2]=""Yes"",""show"",IF(IF(RC[-14]*1>=R15C4,IF(RC[-14]*1<=R16C4,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+R10C18))*1>=R16C5,IF((INDEX(Alignment_Reports,ROW()-20,COLUMN()+R10C18))*1<=R16C6,""show"",""offset too high""),""offset too low""),""stationing too high""),""stationing too low"")=""show"",IF(RC[1]=""Storm"",""show"",IF(RC[1]=""Sanitary"",""Sanitary""," & _
        ",""Don't Show""))"
End Sub
Sub Filter_Storm()
'                MsgBox "Add Storm Formula at Range R21:R420"
    Range("R21").Select
    ActiveCell.FormulaR1C1 = """=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""))"""
    Selection.Copy
    Range("R21:R420").Select
    ActiveSheet.Paste
End Sub

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, 08:48 AM

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

**JBeaucaire** · 02-07-2012, 01: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:

Sub Filter_Sanitary()
    MsgBox "Add Sanitary Formula at Range R21:R420"
    Range("R21:R420").Formula = "=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""))"
End Sub

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

**CityMPLSEmpolyee** · 02-07-2012, 01: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, 02: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:

'Clear out unwanted Data
    Range("GB15:GI" & Rows.Count).ClearContents



'Copping data at temporary location
    Range("HB15").CurrentRegion.Copy



'Clearing data at temporary location
    Range("HB:HI").ClearContents



'Copping calulated data for pasting
    Range("A15").Value = Range("A14").Value


'Sub New_worksheet_Sanitary()
     Range("B18").Value = "SANITARY SEWER STRUCTURES"



    With Range("B18:O18").Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With

**CityMPLSEmpolyee** · 02-09-2012, 08: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

Sub Filter_Storm()

'UnProtecting ActiveSheet with password
    Call UnProtectSheet

    Range("R21:R420").Formula = "=IF(B21=0,"",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"")))"
    
'Protecting ActiveSheet with password
    Call ProtectSheet

End Sub

**CityMPLSEmpolyee** · 02-09-2012, 10: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,