I'm trying to add a column in my data file that will check street address data for various components of the address (e.g., "St.", "Ave.", "Blvd.") and let me know which cases have potentially incomplete data. I developed a formula that works wonderfully to search cells, and when I enter it manually and copy it down through the column manually, all goes well. When I tried to record this action to a macro, however, I keep receiving an error (Runtime error 1004: Application-defined or object-defined error.)
Here's the code that I enter manually (that works):
=IF(ISNUMBER(SEARCH("Ave.",C2))+ISNUMBER(SEARCH("Blvd.",C2))+ISNUMBER(SEARCH("Dr.",C2))+ISNUMBER(SEARCH("Rd.",C2))+ISNUMBER(SEARCH("P.O. Box",C2))+ISNUMBER(SEARCH("Way",C2))+ISNUMBER(SEARCH("Circle",C2))+ISNUMBER(SEARCH("St.",C2))+ISNUMBER(SEARCH("Hwy",C2))+ISNUMBER(SEARCH("Terrace",C2))+ISNUMBER(SEARCH("Place",C2))+ISNUMBER(SEARCH("Lane",C2))+ISNUMBER(SEARCH("Court",C2))+ISNUMBER(SEARCH("Ct.",C2))+ISNUMBER(SEARCH("Loop",C2))+ISNUMBER(SEARCH("Ln.",C2))+ISNUMBER(SEARCH("Ter ",C2))+ISNUMBER(SEARCH("Pl.",C2))+ISNUMBER(SEARCH("S.R.",C2))+ISNUMBER(SEARCH("State Route",C2))+ISNUMBER(SEARCH("Cir.",C2))+ISNUMBER(SEARCH("C.R.",C2))+ISNUMBER(SEARCH("County Route",C2))+ISNUMBER(SEARCH("R.R.",C2))=0,"Check","OK")
And here's how the macro recorder rendered my code (which doesn't work):
ActiveCell.FormulaR1C1Local = _
"=IF(ISNUMBER(SEARCH(""Ave."",RC[-5]))+ISNUMBER(SEARCH(""Blvd."",RC[-5]))+ISNUMBER(SEARCH(""Dr."",RC[-5]))+ISNUMBER(SEARCH(""Rd."",RC[-5]))+ISNUMBER(SEARCH(""P.O. Box"",RC[-5]))+ISNUMBER(SEARCH(""Way"",RC[-5]))+ISNUMBER(SEARCH(""Circle"",RC[-5]))+ISNUMBER(SEARCH(""St."",RC[-5]))+ISNUMBER(SEARCH(""Hwy"",RC[-5]))+ISNUMBER(SEARCH(""Terrace"",RC[-5]))+ISNUMBER(SEARCH(""P" & _
"C[-5]))+ISNUMBER(SEARCH(""Lane"",RC[-5]))+ISNUMBER(SEARCH(""Court"",RC[-5]))+ISNUMBER(SEARCH(""Ct."",RC[-5]))+ISNUMBER(SEARCH(""Loop"",RC[-5]))+ISNUMBER(SEARCH(""Ln."",RC[-5]))+ISNUMBER(SEARCH(""Ter "",RC[-5]))+ISNUMBER(SEARCH(""Pl."",RC[-5]))+ISNUMBER(SEARCH(""S.R."",RC[-5]))+ISNUMBER(SEARCH(""State Route"",RC[-5]))+ISNUMBER(SEARCH(""Cir."",RC[-5]))+ISNUMBER(SEARCH" & _
",RC[-5]))+ISNUMBER(SEARCH(""County Route"",RC[-5]))+ISNUMBER(SEARCH(""R.R."",RC[-5]))=0,""Check"",""OK"")"
From poking around in the forum, it seems my problem relates to FormulaR1C1 and potentially the references that are in the macro code. Any help would be greatly appreciated.
Bookmarks