Hi Folks,

I'm hoping you can help me.

I have a macro that runs fine. I needed to change a formula in cell W2. I recorded a quick macro inserting my formula into cell W2. The formula worked and I got the desired result. However when I have pasted my new code over the old one I get a run time error. Run-time error '1004' Application-defined or object-defined error on the line where my formula has changed.

I'm a little baffled. The new formula works and is correct when entered manually into the worksheet. I didn't write the VBA code for the formula as I was recording the macro.

The formula is quite long so I don't know if that would have an effect when it's being run. I don't know what a Run-time error '1004' Application-defined or object-defined error is and what might cause it. I've posted the formulas and code below but I'm not sure how much help this will be to you.

Old Formula:

=IF(OR(ISNUMBER(SEARCH("Switch Fund Sale",Y2)),ISNUMBER(SEARCH("Switch Fund Sale",T2))),"Switch Fund Sale",IF(OR(ISNUMBER(SEARCH("Switch Fund Purchase",Y2)),ISNUMBER(SEARCH("Switch Fund Purchase",T2))),"Switch Fund Purchase",X2))
Translates to VBA code:

    Range("W2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(ISNUMBER(SEARCH(""Switch Fund Sale"",RC[2])),ISNUMBER(SEARCH(""Switch Fund Sale"",RC[-3]))),""Switch Fund Sale"",IF(OR(ISNUMBER(SEARCH(""Switch Fund Purchase"",RC[2])),ISNUMBER(SEARCH(""Switch Fund Purchase"",RC[-3]))),""Switch Fund Purchase"",RC[1]))"


New Formula: (This works fine when input manually to cell W2)

=IF(OR(ISNUMBER(SEARCH("Switch Fund Sale",Y2)),ISNUMBER(SEARCH("Switch Fund Sale",T2)),AND(ISNUMBER(SEARCH("Switch",T2)),ISNUMBER(SEARCH("Sale",Y2)))),"Switch Fund Sale",IF(OR(ISNUMBER(SEARCH("Switch Fund Purchase",Y2)),ISNUMBER(SEARCH("Switch Fund Purchase",T2)), AND(ISNUMBER(SEARCH("Switch",T2)),ISNUMBER(SEARCH("Purchase",Y2)))), "Switch Fund Purchase",X2))
Translates to VBA code: (Not written by me. This code was created using the record a new macro method whereby I typed my formula into cell W2)

    Range("W2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(ISNUMBER(SEARCH(""Switch Fund Sale"",RC[2])),ISNUMBER(SEARCH(""Switch Fund Sale"",RC[-3])),AND(ISNUMBER(SEARCH(""Switch"",RC[-3])),ISNUMBER(SEARCH(""Sale"",RC[2])))),""Switch Fund Sale"",IF(OR(ISNUMBER(SEARCH(""Switch Fund Purchase"",RC[2])),ISNUMBER(SEARCH(""Switch Fund Purchase"",RC[-3])), AND(ISNUMBER(SEARCH(""Switch"",RC[-3])),ISNUMBER(SEARCH(""Purchase""" & _
        ")), ""Switch Fund Purchase"",RC[1]))"