#N/A的Excel VBA类型不匹配 [英] Excel VBA Type Mismatch for #N/A

问题描述

我有这段代码

    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

其中Cmp.Cells(i，X)表示可能具有#N/A错误(失败的VLOOKUP)的单元格.

Where the Cmp.Cells(i, X) refers to cells that may have #N/A error (a failed VLOOKUP).

是否可以将代码仅以#N/A的形式接收为字符串或将其保留为空?现在，每当引用的一个单元格为#N/A时，该块将失败，并且根本不会添加任何注释文本.

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.

谢谢！

推荐答案

您正在使用单元格的默认属性

You're using the default property of the cell,

Debug.Print Cmp.Cells(i, QRXAll)

例如，这始终引用单元格.Value的属性. .Value实际上是错误类型，Error 2042我认为您可以通过检查来避免

For example this always refers to the cells .Value property. The .Value is actually an error type, Error 2042 which I think you could avoid by checking

CLng(Cmp.Cells(i,QRXA11))

但是这将导致生成2042而不是#N/A文本.

But this will result in 2042 instead of the #N/A text.

如果要获取字符串#N/A:请尝试使用Cmp.Cells(i, QRXAll).Text，它依赖于单元格的.Text属性而不是其.Value.

If you want to get the string #N/A: try using Cmp.Cells(i, QRXAll).Text which relies on the cell's .Text property instead of its .Value.

Debug.Print Cmp.Cells(i, QRXAll).Text

这篇关于#N/A的Excel VBA类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！