CODEWITHSUNDEEP
excelvba
profSteve
profSteve

Reputation: 25

Excel Formula that generates a #REF! error

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.

Upvotes: 1

Views: 309

Answers (2)

Vityata
Vityata

Reputation: 43585

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

Upvotes: 3

JvdV
JvdV

Reputation: 75870

Check = Range("A1").Formula 'Reference to the cell your formula is in

Upvotes: 1

Related Questions