Run time error 1004 Ref: ISERROR

Daminc2 · Feb 14, 2018

I've entered this bit of code into a macro and it's working ok:

Code:

ActiveCell.FormulaR1C1 = "=MID(R[0]C[-2],SEARCH(""Range"",R[0]C[-2])+6,1)"

however, when the cell doesn't have the word Range it returns a 'Value' error.

To overcome this I added an ISERROR function:

Code:

ActiveCell.FormulaR1C1="=ISERROR(MID(R[0]C[-2],SEARCH(""Range"",R[0]C[-2])+6,1),"")"

Which gives me my 1004 problem.

Can anyone explain were my error is please?

steve the fish · Feb 14, 2018

Hi. Yes instead of ,"" for the error you need ,"""" when you use it in VBA.

Norie · Feb 14, 2018

Is it IFERROR you want rather than ISERROR?

Code:

ActiveCell.FormulaR1C1 = "=ISERROR(MID(R[0]C[-2],SEARCH(""Range"",R[0]C[-2])+6,1),"""")"

Daminc2 · Feb 14, 2018

You are right about the IFERROR:

Code:

        ActiveCell.FormulaR1C1 = "=IFERROR(MID(R[0]C[-2],SEARCH(""Range"",R[0]C[-2])+6,1),"")"

and the introduction of the "" worked like a dream:

Code:

ActiveCell.FormulaR1C1 = "=IFERROR(MID(R[0]C[-2],SEARCH(""Range"",R[0]C[-2])+6,1),"""")"

Thanks for a great assist