PDA

View Full Version : Solved: Finding Bad Names



Opv
04-04-2010, 11:29 AM
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
04-04-2010, 11:52 AM
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
04-04-2010, 11:53 AM
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
04-04-2010, 11:55 AM
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
04-04-2010, 11:59 AM
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
04-04-2010, 12:01 PM
Does this help?

Sub Macro1()
For Each cel In Cells.SpecialCells(xlCellTypeFormulas, 16)
msg = msg & cel.Address & vbTab & cel.Formula & vbCr
Next
MsgBox msg
End Sub

Opv
04-04-2010, 12:09 PM
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
04-04-2010, 01:46 PM
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
04-04-2010, 01:49 PM
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
04-04-2010, 02:44 PM
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
04-04-2010, 03:09 PM
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
04-04-2010, 03:52 PM
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
04-04-2010, 05:57 PM
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
04-04-2010, 05:58 PM
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.