Excel Formula that generates a #REF! error

Question

In Microsoft Excel I have a cell that generates a #REF! error (because it refers to a worksheet that's been deleted). I want to write VBA code that will retrieve the formula that was in that cell. Has the REF error wiped out the original formula so it's lost forever? I know that IsError(ActiveCell.Value) will tell me whether a cell has an error, and I know that ActiveCell.Value will give me the error number (2023 for #REF), but that still leaves me without the original formula. If I had the actual formula, I could change the sheet name and put it back in the troublesome cell.

The #REF! part should only have wiped out the sheet name. If you want to use a different sheet name, just replace #REF! with whatever that sheet name is. — Rory

Vityata Vityata · Accepted Answer · 2018-05-24T14:00:25

Has the #REF! error wiped out the original formula so it's lost forever?

Yes! You cannot reverse it.

However, if you are looking for an easy way, to keep the formulas, before deleting workbook that they refer to, you may add a ' sign in front of each formula. Thus, the formulas would be saved as text and you would avoid #REF!:

Public Sub AllFormulasToText()

    Dim myCell As Range

    For Each myCell In ActiveSheet.UsedRange
        If myCell.HasFormula Then myCell.Value = "'" & myCell.Formula
    Next myCell

End Sub

Excel Formula that generates a #REF! error

2 Answers