Vba Code to Remove #Name? error in all cells entire worksheet

[intellitechdataservices]

Hello Everyone,
I am unable to remove #Name? error in my excel sheet. I need any vba code show that the cells can show actual text including character =
See the screenshot of the error.

 

[CephasOz]

You don't need a macro to see that. Just press Ctrl-~ to show the cell contents, and then press it again when you're finished.

 

[intellitechdataservices]

You don't need a macro to see that. Just press Ctrl-~ to show the cell contents, and then press it again when you're finished.

Yes but it needs to be done in every sheets multiple times if i have 200 sheets in the workbook with same error. So thats y i need a shortcut or vba. Any suggestion

 

[CephasOz]

As to a shortcut, you could use GoTo.

1. Press F5 to bring up the dialog box,
2. click the "Special" button,
3. click the "Formulas" radio button
4. under "Formulas" take the ticks out of "Numbers", "Text", and "Logicals" (so now only "Errors" has a tick in that group), then
5. click the "OK" button.

The cells should now be selected, and once you've checked that you've got the right cells, you can just press the Delete key on the keyboard.

 

[CephasOz]

... or would this code suit you better?

Sub DeleteNameErrors()
    Dim wksToCheck As Worksheet
    Dim rngCell As Range
    Dim xlcPrev As XlCalculation
    '
    xlcPrev = Application.Calculation
    Application.Calculation = xlCalculationManual
    For Each wksToCheck In ThisWorkbook.Worksheets
        For Each rngCell In wksToCheck.UsedRange
            If rngCell.HasFormula Then
                If (rngCell.Text = "#NAME?") Then
                    If (rngCell.Formula2R1C1 = "=yes") Then
                        rngCell.ClearContents
                    End If
                End If
            End If
        Next
    Next
    Application.Calculation = xlcPrev
    MsgBox "Finished!", vbInformation + vbOKOnly, "Delete Name Errors"
End Sub

 

[intellitechdataservices]

I dont want to delete #Name? error , I want to show actual text Ctrl~ worked! but this vba not working
Ctrl~ worked . Go to also worked

 

[CephasOz]

Okay, if you just want to show the actual text, rather than the #NAME? error, this code will do that.

Sub ShowNameErrors()
    Dim wksToCheck As Worksheet
    Dim rngCell As Range
    Dim xlcPrev As XlCalculation
    '
    xlcPrev = Application.Calculation
    Application.Calculation = xlCalculationManual
    For Each wksToCheck In ThisWorkbook.Worksheets
        For Each rngCell In wksToCheck.UsedRange
            If rngCell.HasFormula Then
                If (rngCell.Text = "#NAME?") Then
                    rngCell.Value = "'" & rngCell.Formula2R1C1
                End If
            End If
        Next
    Next
    Application.Calculation = xlcPrev
    MsgBox "Finished!", vbInformation + vbOKOnly, "Show Name Errors"
End Sub

 

[intellitechdataservices]

Will test this and tell

 

[intellitechdataservices]

It didnt worked vba

 

[intellitechdataservices]

Finally it worked with some modifications

Sub ShowNameErrors()
Dim wksToCheck As Worksheet
Dim rngCell As Range
Dim xlcPrev As XlCalculation

' Store the previous calculation mode
xlcPrev = Application.Calculation
Application.Calculation = xlCalculationManual

' Loop through each worksheet in the workbook
For Each wksToCheck In ThisWorkbook.Worksheets
' Loop through each cell in the used range of the worksheet
For Each rngCell In wksToCheck.UsedRange
' Check if the cell contains a formula and if its text is "#NAME?"
If rngCell.HasFormula Then
If rngCell.Text = "#NAME?" Then
' Replace the cell value with the formula preceded by an apostrophe
rngCell.Value = "'" & rngCell.Formula
End If
End If
Next rngCell
Next wksToCheck

' Restore the previous calculation mode
Application.Calculation = xlcPrev

' Display a message box to indicate completion
MsgBox "Finished!", vbInformation + vbOKOnly, "Show Name Errors"
End Sub