VBA - Find, Replace #NAME Errors with Formula Value

[bs0d]

Can someone help me with a function that would evaluate the contents of a sheet, find any #NAME errors, and replace contents with " ' " + contents to show the value (rather than the error)?

Here's start, but it's more of an algorithm than functional code. Thoughts here?

Dim sh As Worksheet
Dim fnd As Variant
Dim rpl As Variant

fnd = CVErr(ActiveWorkbook.Worksheets.sh.Cells)
rpl = "'" & ActiveWorkbook.Worksheets.sh.Cells.Value

For Each sh In ActiveWorkbook.Worksheets
  sht.Cells.Replace what:=fnd, Replacement:=rpl, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next sh

 

[Scott Huish]

If you're getting a #NAME error, it means you are trying to reference a function or named range that does not exist and Excel can't evaluate it.
Can you give an example of something that is causing this error?

 

[StephenCrump]

Not sure why, but perhaps something like:

For Each sh In Worksheets
    On Error Resume Next
    Set rngErrors = sh.Cells.SpecialCells(xlFormulas, xlErrors)
    On Error GoTo 0
    If Not rngErrors Is Nothing Then
        For Each rng In rngErrors
            If rng.Value = CVErr(xlErrName) Then
                rng.Value = "'" & rng.Formula
                rng.Interior.Color = vbRed
            End If
        Next rng
    End If
Next sh

 

[bs0d]

To answer why, I'm dealing with a report that's auto-generated by a piece of software. The report includes a repeated format/template for multiple items. When put to Excel, the text-to-columns isn't always correct. So for some of the items, the name of the item gets split across more than one column.

I'm creating a macro that will go in and cleanup the format and make it more user friendly. Some of the item names include dashes (-). So in one specific example, a cell value became: "=-ALT" (which resulted in the #NAME error). I assume Excel added the equal sign on it's own. I'd still like to preserve that piece of the item name, so my idea was to just add "'" before, and then I can concatenate the item name back together.

Maybe this isn't the right approach. I'd appreciate any additional thoughts. Thanks again