Error in macro when copying formula down column

[newnoise]

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

Please Login or Register  to view this content.

And here's how the macro recorder rendered my code (which doesn't work):

Please Login or Register  to view this content.

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.

[davesexcel]

Was the active cell the same place as the original cell?

[newnoise]

Oh, I left off that portion of the formula. Prior to entering the formula, I selected cell H2. The formula checks for issues in cell C2.

[davesexcel]

Try this

Please Login or Register  to view this content.

To split a line of code use 1 space " _" and the underscore line, don't use "&"

Also the bottom line of your code is missing the item to search for.
Did you use the macro recorder and type the formula?
If you had the correct formula in H2, with H2 selected, start the macro recorder, go to the formula bar and hit enter. turn off the macro recorder.
That is your formula in code form.

[newnoise]

Thanks a lot!