Adding formula to excisting formulas

[Newbiiiie]

Hello There


Im quite new to VBA.



I Have a problem
My Workbook is 20 Sheets, and in each Sheet there is alot of formulars.

I need to add af "if.error" formula to the aldready excisting formulas.

Any one got an idea?

[p45cal]

Try this on a copy of your workbook. It works on the Active workbook, so beware!:

Sub addIfError()
For Each sht In Worksheets
  For Each cll In sht.Cells(1).SpecialCells(xlCellTypeFormulas).Cells
    cll.Formula2 = "=Iferror(" & Mid(cll.Formula2, 2) & ","""")"
  Next cll
Next sht
End Sub

[Newbiiiie]

Hello

Im getting "out of memory"

[p45cal]

You could try adding:

Application.Calculation = xlCalculationManual

at the start and

Application.Calculation = xlCalculationAutomatic

at the end.

[Paul_Hossler]

Or try just .Formula without the "2" and .UsedRange to see if the results are different

Sub addIfError()

Application.Calculation = xlCalculationManual

For Each sht In Worksheets
On Error Resume Next  ' in case no formulas on a sheet
For Each cll In sht.UsedRange.SpecialCells(xlCellTypeFormulas).Cells
cll.Formula = "=Iferror(" & Mid(cll.Formula, 2) & ","""")"
Next cll
On Error Goto 0
Next sht
Application.Calculation = xlCalculationAutomatic
End Sub