VBA insert IF(ISERROR( into Block of Cells

[Delvesy888]

Hi guys,

I am relatively new to VBA and am trying to create a function that takes my original cell with function "Original Cell Function" and changes it to IF(ISERROR(Original Cell Function),"",Original Cell Function).

I want this to be done on a block of cells, from A1 to R18

The VBA code I have tried is:

Sub IFERROR()

For j = 1 to 18
For i =1 to 18

what_in_the_cell = Range ("A1").Formula

new_cell_formula = "IF(ISERROR(" & what_in_the_cell & " ,"", " & what_in_the_cell & ")"

Range("A1").Offset(i,j).Formula

Next i
Next j

End Sub


I hope it is clear what I am trying to do.
But when I am in VBA and click 'run' I get an error:- "Compile error: Invalid use of property"

Then the Sub IFERROR() gets highlighted yellow. But I am unsure on the problem.

Thanks in advance for any help!

 

[MARK858]

Try

Sub addIffErr()
Dim i As Range
 For Each i In Range("A1:R18").SpecialCells(xlCellTypeFormulas)
            i.Formula = "=IFERROR(" & Right(i.Formula, Len(i.Formula) - 1) & ","""")"
        Next
End Sub