View Full Version : Solved: Finding Bad Names
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?
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?
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
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.
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
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!)_
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.
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
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.
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.