vba 如何使用#NAME 解决 excel 错误 2029？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11760561/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to resolve excel error 2029 with #NAME?
提问by Adrian
I read an excel file and on a cell I got a text like this:
我读了一个 excel 文件，在一个单元格上我得到了这样的文本：
"=- Bla Bla Bla".This will not be recognize and will show #NAME?
"=- Bla Bla Bla".这将不会被识别并会显示 #NAME?
So if you need to read some cell and get it into database this error in the file will show as Error 2029. The script will freeze.
So, can I pre-replace the content if I get =or -chars in the cell so I validate the content before I read it and get the error. Can I pass over it!
因此，如果您需要读取某个单元格并将其放入数据库，则文件中的此错误将显示为Error 2029. 脚本将冻结。因此，如果我在单元格中获取=或-字符，我是否可以预先替换内容，以便我在阅读内容并收到错误之前验证内容。能不能传过去！
I need cell validation
我需要单元格验证
Private Sub Worksheet_Change(ByVal Target As Range)
c = Target.Cells.Column
r = Target.Cells.Row
'validate cell
End If
Thank you!
谢谢！
采纳答案by Adrian
I got it working
我让它工作了
'verify cell for data
Private Sub Worksheet_Change(ByVal Target As Range)
c = Target.Cells.Column
r = Target.Cells.Row
If IsError(Target.Worksheet.Cells(r, c)) Then
MsgBox "You have a validation Error!"
Target.Worksheet.Cells(r, c) = ""
End If
End Sub
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Sub Sample()
Dim sTemp As String
With Sheets("Sheet1")
'~~> Check if cell has error
If IsError(.Range("A1").Value) Then
'~~> Check if it is a 2029 error
If .Range("A1").Value = CVErr(2029) Then
'~~> Get the cell contents
sTemp = Trim(.Range("A1").Formula)
'~~> Remove =/-
Do While Left(sTemp, 1) = "=" Or Left(sTemp, 1) = "-"
sTemp = Trim(Mid(sTemp, 2))
Loop
'~~> Either put it in back in the cell or do
'~~> what ever you want with sTemp
.Range("A1").Formula = sTemp
End If
End If
End With
End Sub
回答by Zairja
You can also add Data Validation to your worksheet to prevent things from being entered that way. Under "Validation criteria", allow "Custom" and enter this formula:
您还可以向工作表添加数据验证，以防止以这种方式输入内容。在“验证标准”下，允许“自定义”并输入以下公式：
=NOT(OR(IF(EXACT(LEFT(TRIM(A1),1),"-"),1,0),IF(EXACT(LEFT(TRIM(A1),1),"="),1,0)))
If the value entered in the cell begins with a "-" or "=" (with leading spaces), then it won't accept it. If the person typed in =123, then it would be valid since it's treated like a formula and will only see it as 123.
如果在单元格中输入的值以“-”或“=”（带前导空格）开头，则不会接受它。如果该人输入了=123，那么它将是有效的，因为它被视为一个公式并且只会将其视为123.
If you don't want the cell to contain =or -at all, then try this for your data validation formula. Again, it won't be able to catch a formula like =NOW()or =-hi(it would see #NAME?for -hi), but it will reject =-123since it sees -123. If you have the cells formatted as Text, then it will reject any cell with =or -inside because values starting with =aren't treated like formulas.
如果你不想让细胞含有=或-根本，那就试试这个为你的数据验证公式。同样，它无法捕捉到像=NOW()or =-hi（它会看到#NAME?for -hi）这样的公式，但它会拒绝，=-123因为它看到了-123。如果您将单元格格式设置为Text，那么它将拒绝任何带有=或-内部的单元格，因为以 开头的值=不会被视为公式。
=NOT(OR(ISNUMBER(FIND("=",A18)),ISNUMBER(FIND("-",A18))))
