Runtime error 1004 when putting formula into cell

Question

I have just changed my code where I put a formula into a cell and it now brings up Runtime error 1004 (Application defined or Object defined error). My new code is:

ActiveCell.FormulaR1C1 = _
    "=IF(IsError(VLOOKUP(RC[3],TransactionTypes!C:C[1],2,0)),""MISC"",IF(ISNUMBER(SEARCH(""AX"",(RC[2],2))),""Amex"",VLOOKUP(RC[3],TransactionTypes!C:C[1],2,0)))"

The code that previously worked fine was:

'ActiveCell.FormulaR1C1 = _
    "=IF(IsError(VLOOKUP(RC[3],TransactionTypes!C:C[1],2,0)),""MISC"",IF(LEFT(RC[2],2)=""AX"",""Amex"",VLOOKUP(RC[3],TransactionTypes!C:C[1],2,0)))"

The part I changed was IF(LEFT(RC[2],2)=""AX"",""Amex"" into IF(ISNUMBER(SEARCH(""AX"",(RC[2],2))),""Amex"" but I can't see why this is throwing up the error.

if you have any time this type of problem you should paste the formula manually to the cell to see whether it's correct. That's the easiest way to find the error in it. — Máté Juhász

Rory Rory · Accepted Answer · 2016-05-18T14:10:35

It appears you left in part of the LEFT statement. You need:

ActiveCell.formular1c1 = _
    "=IF(IsError(VLOOKUP(RC[3],TransactionTypes!C:C[1],2,0)),""MISC"",IF(ISNUMBER(SEARCH(""AX"",RC[2])),""Amex"",VLOOKUP(RC[3],TransactionTypes!C:C[1],2,0)))"

Runtime error 1004 when putting formula into cell

1 Answers