Solved: Finding Bad Names

[Opv]

Has anyone ever come up with a subroutine to scan a worksheet to find broken or bad names? I'm receiving a $NAME? error, and I have combed the cells as best I can and I can't find one that appears to be invalid. Since the worksheet is rather extensive, I was wondering if VBA could be used to identify the cell containing the culprit?

[Ago]

I know i have seen that error, but i dont know what was wrong when i saw it.
Could you post a "error" for us to try on?

[SamT]

First: select the Cell showing the $Name and Tool: Formula Auditing; Trace Precedents

In a blank worksheet, Insert; Name; Paste; Paste List.

Also Tools; Options; View; Formulas

[Opv]

That's part of my problem. There's not just one cell. There are numerous cells all of a sudden showing the NAME error. I'm having to comb through each cell one at a time trying to find the bad name. I know I've made some recent changes so I've been trying to focus on those columns.

[Opv]

Well, this is odd. I double clicked in one of the cells that had the NAME error and then when I clicked out of the cell, the NAME error in that cell and all the other cells ON THAT ROW went away. I went down the column doing that to each cell in the same column and that resolved all the errors one row at a time. I changed nothing. Wonder what could have caused that?

[mdmackillop]

Does this help?
[VBA]
Sub Macro1()
For Each cel In Cells.SpecialCells(xlCellTypeFormulas, 16)
msg = msg & cel.Address & vbTab & cel.Formula & vbCr
Next
MsgBox msg
End Sub

[/VBA]

[Opv]

Does this help?

Hmmm. Got an error trying to run that routine. Per my previous reply, all of the NAME errors are gone now. I didn't change anything, so I don't know what made them clear up. Thanks for the help.

[mdmackillop]

That code will error if there are no errors. You can easily create a name error by typing =XXX() in a cell (assuming you have no function of that name!)_

[Opv]

That code will error if there are no errors. You can easily create a name error by typing =XXX() in a cell (assuming you have no function of that name!)_

Thanks.

[Opv]

That code will error if there are no errors. You can easily create a name error by typing =XXX() in a cell (assuming you have no function of that name!)_

I figured out why I received an error running the suggested script. I had Option Explicit set and the variables cel and msg had not been declared. I Dimed them and it works fine.

Thanks again,

Opv

[ZVI]

Well, this is odd. I double clicked in one of the cells that had the NAME error and then when I clicked out of the cell, the NAME error in that cell and all the other cells ON THAT ROW went away. I went down the column doing that to each cell in the same column and that resolved all the errors one row at a time. I changed nothing. Wonder what could have caused that?

Such issue sometimes happens with Analysis ToolPak Add-In functions in Excel formulas.
Try to find & replace the 1st (equality) character of each formula by itself to reanimate it.
Find text: =
Replace by: =
Apply it to all or selected cells

[mikerickson]

The one row an a time thing suggests that looking at those names with the appropriate relative/absolute referencing would be a clue to where the error is.

[Opv]

Such issue sometimes happens with Analysis ToolPak Add-In functions in Excel formulas.
Try to find & replace the 1st (equality) character of each formula by itself to reanimate it.
Find text: =
Replace by: =
Apply to all or selected cells

Thanks, I'll keep that in mind if it happens again.

David

[Opv]

The one row an a time thing suggests that looking at those names with the appropriate relative/absolute referencing would be a clue to where the error is.

Thanks. Fortunately, the issue has corrected itself. Of course, there's always next time.