Adding formula to excisting formulas

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?

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

Code:

---

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

---

Hello

Im getting "out of memory"

You could try adding:

Code:

---

Application.Calculation = xlCalculationManual

---

at the start and

Code:

---

Application.Calculation = xlCalculationAutomatic

---

at the end.

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

Code:

---

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

---