View Full Version : Adding formula to excisting formulas
Newbiiiie
10-19-2021, 11:58 PM
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
10-20-2021, 09:04 AM
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
10-27-2021, 01:09 AM
Hello
Im getting "out of memory"
p45cal
10-27-2021, 03:38 AM
You could try adding:
Application.Calculation = xlCalculationManual
at the start and
Application.Calculation = xlCalculationAutomatic
at the end.
Paul_Hossler
10-27-2021, 07:13 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.