Remove #REF! Error from formula with VBA without deleting formula

    So after royUK kindly helped me get some bits of my macro working, I'm now looking at how I can run my macro more efficiently.

    Using a steptimer method (in the module called 'Check'), I've managed to work out that >95% of the time it takes my code to run is dedicated to 4 lines of code.


    The full code is:


    With the AppSetting code provided by Roy:


    My code is being slowed by this segment:


    Code
    'Remove #REF! errors that have resulted due to deleting columns non relevant to sheet, however retain formula    
Dim Calcs As Range
For Each Calcs In .Range("D11:G5010").SpecialCells(xlFormulas)
Calcs.Formula = Replace(Calcs.Formula, "#REF!", "0")
Next Calcs

    Just prior to this segment of code, there is a section which deletes a number of columns which are not specific to the worksheet currently being worked on (and range of columns can change for each worksheet). As a result, the Formulas in D11:G5010 end up with #REF errors through them. I want to remove the reference error (currently being done by replacing it with 0) and retain the formula in the cell (as opposed to deleting the formula or cell).


    Any suggestions on how to do this more efficiently?


    I've also attached a sample of the workbook with most of the loop worksheets deleted to reduce file size and time to execute.

    I'm not sure that you can replace the REF error like that. It's generated by the formulas.


    I can't actually open the attached file, can you try reattaching it


    EDIT: I've managed to open it but Excel reports errors in it and removes them.

    Try this


    Code
    'Remove #REF! errors that have resulted due to deleting columns non relevant to sheet, however retain formula
Dim Calcs As Range
On Error Resume Next
For Each Calcs In .Range("D11:G5010").SpecialCells(xlFormulas, xlErrors)
Calcs.Formula = Replace(Calcs.Formula, "#REF!", "0")
Next Calcs
On Error Resume Next

    Apologies Roy, for some reason there is corrupted data in that upload. I've uploaded again here as a new file and with fewer worksheets.


    The current method I have does work - I'm really wondering if there's a faster way to do it. I assume the reason it's currently so slow is because it's individually looking at each cell in the range and writing over the #REF error with '0'. Is there a way I could do this with an array or something that would be faster/more efficient?

    Hello,


    You could test following

    Code
    Sub TestDeleteErrors()
    Range("D11:G5010").SpecialCells(xlCellTypeFormulas, 16).ClearContents
End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    roy


    The code you suggested above doesn't work - the #REF! errors remain in the code and it appears to skip over replacing them. My original code did work - it was just super slow, I suspect because of the the 'For each calc' part.


    Carim


    Your code returns an error 'No cells were found' despite there clearly being #REF! errors in cells. I suspect however even if your code were to run, it would actually wipe the formula from the cell? I wish to retain the formulas, just remove the #REF! error inside it.


    I was thinking something like the below would work however I still get the same error of 'No cells were found' if I try to replace all 4 lines of original code. It's almost as if it's not searching the formulas within the range properly.

    Code
    .Range("D10:G5010").SpecialCells(xlFormulas, xlErrors).Replace "#REF!", "0", xlPart

    Hello,


    Just tested the macro which does function smoothly ;)...


    But, the suggested macro only handles actual Errors ... it does not identify Text such as N/A or REF ....:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login