+ Reply to Thread
Results 1 to 5 of 5

Find formula errors in range

  1. #1
    Forum Contributor
    Join Date
    07-26-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2016 Office 365 ProPlus
    Posts
    822

    Find formula errors in range

    I have a macro that imports data and then in other columns does several formula's on the data.

    Since it are a lot of lines and it is part of a bigger process i want the code to generate a warning message
    at the end that formula errors (#N/A, #VALUE etc) have been detected on the source data.
    So the user then knows he needs to update mapping/conversion tables in the model.
    Thanks.

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Find formula errors in range

    Maybe wrap the vba generated formulas in IFERROR?

    =IFERROR(oldformula,"warning message")

  3. #3
    Forum Contributor
    Join Date
    07-26-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2016 Office 365 ProPlus
    Posts
    822

    Re: Find formula errors in range

    hmm yeah but this would show the message directly when the formula is calculated right?
    Say I have 7000 lines and 100 are in error on 3 columns the user will get 300 messageboxes saying there is an error?

    Rather would have 1 message box at the end of the code to check if in a certain range there are errors.

  4. #4
    Forum Contributor
    Join Date
    07-26-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2016 Office 365 ProPlus
    Posts
    822

    Re: Find formula errors in range

    Maybe to clarrify more.

    This code:
    Please Login or Register  to view this content.
    In the data this code will select the formula errors.
    However I do not want to select it, I rather would like to count the occurences and "catch" the number of errors in a variable.

    Then at the end of the code I could have someting like :
    Please Login or Register  to view this content.
    Maybe this makes more clear what I am looking for.

  5. #5
    Forum Contributor
    Join Date
    07-26-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2016 Office 365 ProPlus
    Posts
    822

    Re: Find formula errors in range

    Ok I managed to solve it myself.
    And for anybody who is interested in my solution this is the code:
    Please Login or Register  to view this content.
    My errors to count actually only were needed in column A (but it also would work with A:B, A:F etc.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to check if a Multi-Cell Range contains any errors
    By Masa1989 in forum Excel General
    Replies: 3
    Last Post: 07-21-2015, 11:58 PM
  2. [SOLVED] Find unique values from range ( populated cells only) & not count zeros or NA errors VBA
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2014, 06:50 AM
  3. Find all tabs with formula errors
    By antiquest in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-28-2012, 12:09 PM
  4. Copying sheet with relative formula named range causes errors.
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-19-2011, 08:44 AM
  5. Replies: 9
    Last Post: 03-16-2010, 03:34 PM
  6. Formula to find errors
    By randell.graybill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2010, 03:21 AM
  7. How do i make a sum formula ignore #div/0! errors in the range
    By shat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-22-2006, 09:50 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1