Error 1004 when using =AND(ISERROR) formula in macro

Question

I'm recording a macro to automate some Excel reports and have encountered the following bug whenever I try and run an iserror(search) formula:

Run-time error '1004': Application-defined or object-defined error

I have two lists. The formula iterates through the first list and compares the values with those of the second list, hiding any matching values.

The formula in Excel is like this only with a wider criteria range:

=AND(ISERROR(SEARCH($B$3212,B2)),ISERROR(SEARCH($B$3213,B2)))

It works perfectly when I insert the formula directly into the spreadsheet cell however I get an error when I record and later run the macro using the same formula.

EDIT 2

I got the formula insertion to work through the macro but now I cannot filter the data as before, even when I do it manually without the macro.

Below is a link to a picture giving an example of the type of lookup I'm trying to achieve, previously it worked perfectly and removed all the rows which contained a string from the 'to remove list' now I cannot get it to filter at all. I've tried removing the macro after saving in notepad in case the file had become corrupted but it still does not filter as before. What could be causing this?

This is how the lookup works

Cell [A13] would contain the aforementioned ISERROR formula in this example.

runtime error 1004 usually means you haven't defined an object correctly. You'll need to post the code. — Dave
Runtime error 1004 is also generated when the formula you are trying to insert into a cell is not a valid Excel formula. But, as @Dave says, you'll need to post the code if you want help. (Or you can change your your ....Formula = "=....." to MsgBox "=....." to see what formula you are trying to insert - you may be able to spot the problem yourself.) — YowE3K
As a rule of thumb it isn't a good idea to use worksheet solutions in VBA or v.v. Excel's ISERROR function demonstrates the point, as does your question. VBA offers better solutions, meaning clearer syntax which is easier to debug, so that you wouldn't even have this question had you used one of them instead of trying to force VBA into the ISERROR function. The same could be said for SEARCH. The fault is in the design. Your design is made for the worksheet. You shouldn't expect the macro recorder to be able to translate it correctly. — Variatus
@Variatus - I'm not sure what you mean by "force VBA into the ISERROR function" (etc). If the VBA code is just setting a cell's formula to be a string (which is what would have been recorded) then it won't matter what the contents of that string are, providing the string contains something that Excel (not VBA) can interpret. — YowE3K
@YowE3K - OP said that the formula is working on the worksheet but fails when recorded into VBA and run from the VB platform. Hence I understood that the problem results from VBA being asked to interpret an Excel function. Perhaps I got it wrong. — Variatus

J. Garth J. Garth · Accepted Answer · 2017-04-08T23:56:50

This formula doesn't translate well to VBA in its current form. You should use the VBA Instr function instead of the worksheet function Search.

Function FindSubstring() As Boolean

    Dim rngFindText As Range
    Dim rngWithinText As Range

    Set rngFindText = Sheet1.Range("B3212")
    Set rngWithinText = Sheet1.Range("B2")

    FindSubstring = InStr(rngWithinText, rngFindText)

End Function

Sub foobar()

    Debug.Print FindSubstring

End Sub

Error 1004 when using =AND(ISERROR) formula in macro

3 Answers