Run time error 1004 Ref: ISERROR

[Daminc2]

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

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:

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]

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

 

[Norie]

Is it IFERROR you want rather than ISERROR?

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

 

[Daminc2]

You are right about the IFERROR:

        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:

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

Thanks for a great assist