Consulting

Results 1 to 5 of 5

Thread: Adding formula to excisting formulas

  1. #1

    Question 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?

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    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.

  3. #3
    Hello

    Im getting "out of memory"

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    You could try adding:
    Application.Calculation = xlCalculationManual
    at the start and
    Application.Calculation = xlCalculationAutomatic
    at the end.
    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.

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,040
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •