#N/A 的 Excel VBA 类型不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18924948/
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
Excel VBA Type Mismatch for #N/A
提问by csg
I have this chunk of code
我有这段代码
With Data.Cells(rowMatch, GWECol)
.Value = Cmp.Cells(i, GWENetPr)
.AddComment
.Comment.Text Text:=UCase(Environ("UserName")) & ":" & vbNewLine _
& "Comment: " & Cmp.Cells(i, CommCol) & vbNewLine _
& "Transaction: " & Cmp.Cells(i, QRTran) & vbNewLine _
& "QR Pr: " & Cmp.Cells(i, QRPr) & vbNewLine _
& "QR WD: " & Cmp.Cells(i, QRWD) & vbNewLine _
& "QR WD All: " & Cmp.Cells(i, QRWDA) & vbNewLine _
& "QR XPr: " & Cmp.Cells(i, QRXPr) & vbNewLine _
& "QR XAll: " & Cmp.Cells(i, QRXAll) & vbNewLine _
& "GWE Pr: " & Cmp.Cells(i, GWEPr) & vbNewLine _
& "GWE All: " & Cmp.Cells(i, GWEAll) & vbNewLine _
& "GWE XPr: " & Cmp.Cells(i, GWEXPr) & vbNewLine _
& "GWE XAll: " & Cmp.Cells(i, GWEXAll)
.Comment.Shape.TextFrame.AutoSize = True
End With
Where the Cmp.Cells(i, X) refers to cells that may have #N/A error (a failed VLOOKUP).
其中 Cmp.Cells(i, X) 指的是可能有 #N/A 错误（失败的 VLOOKUP）的单元格。
Is it possible to have the code just take in the #N/A as a string or just leave it empty? Right now, whenever one of the cells referenced is #N/A, the chunk will fail and no comment text will be added at all.
是否可以让代码只将 #N/A 作为字符串输入或将其留空？现在，只要引用的单元格之一是#N/A，块就会失败并且根本不会添加任何注释文本。
Thanks!
谢谢！
回答by David Zemens
You're using the default property of the cell,
您正在使用单元格的默认属性，
Debug.Print Cmp.Cells(i, QRXAll)
Debug.Print Cmp.Cells(i, QRXAll)
For example this always refers to the cells .Valueproperty. The .Valueis actually an error type, Error 2042which I think you could avoid by checking
例如，这始终是指单元格.Value属性。该.Value实际上是一个错误类型，Error 2042我想你可以通过检查避免
CLng(Cmp.Cells(i,QRXA11))
CLng(Cmp.Cells(i,QRXA11))
But this will result in 2042instead of the #N/Atext.
但这将导致2042而不是#N/A文本。
If you want to get the string #N/A: try using Cmp.Cells(i, QRXAll).Textwhich relies on the cell's .Textproperty instead of its .Value.
如果您想获取字符串#N/A：尝试使用Cmp.Cells(i, QRXAll).Textwhich 依赖于单元格的.Text属性而不是它的.Value.
Debug.Print Cmp.Cells(i, QRXAll).Text
Debug.Print Cmp.Cells(i, QRXAll).Text
回答by Sorceri
use IsError to check to see if the cells has #N/A
使用 IsError 检查单元格是否有 #N/A
if IsError(Cmp.Cells(i, GWENetPr)) then
'give it a valid value
else
'use the value int he cell
end if
'start with statement
example
例子
With Data.Cells(rowMatch, GWECol)
If IsError(Cmp.Cells(i, GWENetPr)) Then
.Value = "" 'or #N/A
Else
.Value = Cmp.Cells(i, GWENetPr)
End If
.AddComment
.Comment.Text Text:=UCase(Environ("UserName")) & ":" & vbNewLine _
& "Comment: " & Cmp.Cells(i, CommCol) & vbNewLine _
& "Transaction: " & Cmp.Cells(i, QRTran) & vbNewLine _
& "QR Pr: " & Cmp.Cells(i, QRPr) & vbNewLine _
& "QR WD: " & Cmp.Cells(i, QRWD) & vbNewLine _
& "QR WD All: " & Cmp.Cells(i, QRWDA) & vbNewLine _
& "QR XPr: " & Cmp.Cells(i, QRXPr) & vbNewLine _
& "QR XAll: " & Cmp.Cells(i, QRXAll) & vbNewLine _
& "GWE Pr: " & Cmp.Cells(i, GWEPr) & vbNewLine _
& "GWE All: " & Cmp.Cells(i, GWEAll) & vbNewLine _
& "GWE XPr: " & Cmp.Cells(i, GWEXPr) & vbNewLine _
& "GWE XAll: " & Cmp.Cells(i, GWEXAll)
.Comment.Shape.TextFrame.AutoSize = True
End With
回答by JDB still remembers Monica
Disclaimer: I have done some VBA programming, but I wouldn't call myself an expert.
免责声明：我已经完成了一些 VBA 编程，但我不会称自己为专家。
This may be overly simplistic, but you could just assign each value to a variable and then assign the variables to the comment. If any one value is N/A, at least the rest of your values will still be assigned to the comment. I perfer this kind of solution as it ensures that a single error will not derail the entire operation.
这可能过于简单，但您可以只将每个值分配给一个变量，然后将这些变量分配给注释。如果任何一个值为 N/A，至少您的其余值仍将分配给评论。我更喜欢这种解决方案，因为它确保单个错误不会破坏整个操作。
Dim vComment As String
Dim vTransaction As String
Dim vQRPr As String
Dim vQRWD As String
' Etc.
vComment = Cmp.Cells(i, CommCol).Text
vTransaction = Cmp.Cells(i, QRTran).Text
vQRPr = Cmp.Cells(i, QRPr).Text
vQRWD = Cmp.Cells(i, QRWD).Text
' Etc.
.Comment.Text Text:=UCase(Environ("UserName")) & ":" & vbNewLine _
& "Comment: " & vComment & vbNewLine _
& "Transaction: " & vTransaction & vbNewLine _
& "QR Pr: " & vQRPr & vbNewLine _
& "QR WD: " & vQRWD & vbNewLine
' Etc.
Edited: Thanks to Davidfor pointing out that the .Textproperty should be used
编辑：感谢大卫指出.Text应该使用该属性
回答by Joe
You can use IIfto use a specific value if there is an error:
IIf如果出现错误，您可以使用特定值：
& "Comment: " & IIf(IsError(Cmp.Cells(i, CommCol)),"",Cmp.Cells(i, CommCol)) & vbNewLine _
