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?
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!: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
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Hello
Im getting "out of memory"
You could try adding:at the start andApplication.Calculation = xlCalculationManualat the end.Application.Calculation = xlCalculationAutomatic
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3