从 VBA 代码检查 Excel 单元格中的 #N/A [英] Checking for #N/A in Excel cell from VBA code
问题描述
我正在遍历一系列单元格,这些单元格包含两位小数.我需要检查单元格是否包含#N/A",如果是,我需要跳过它.问题是,当一个单元格包含一个有效数字时,我下面的 if 条件会引发类型不匹配错误".我怎样才能避免这种情况?
I'm iterating through a range of cells which hold numbers with 2 decimal places. I need to check if the cell holds '#N/A', and if it does, I need to skip it. The problem is, when a cell holds a valid number, my if condition below throws a 'Type mismatch error'. How can I avoid this?
If (ActiveWorkbook.Sheets("Publish").Range("G4").offset(offsetCount, 0).Value <> CVErr(xlErrNA)) Then
'do something
End If
推荐答案
首先检查错误(N/A 值),然后尝试与 cvErr() 进行比较.您正在比较两个不同的东西,一个值和一个错误.这可能有效,但并非总是如此.简单地将表达式转换为错误可能会导致类似的问题,因为它不是真正的错误,只是取决于表达式的错误值.
First check for an error (N/A value) and then try the comparisation against cvErr(). You are comparing two different things, a value and an error. This may work, but not always. Simply casting the expression to an error may result in similar problems because it is not a real error only the value of an error which depends on the expression.
If IsError(ActiveWorkbook.Sheets("Publish").Range("G4").offset(offsetCount, 0).Value) Then
If (ActiveWorkbook.Sheets("Publish").Range("G4").offset(offsetCount, 0).Value <> CVErr(xlErrNA)) Then
'do something
End If
End If
这篇关于从 VBA 代码检查 Excel 单元格中的 #N/A的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!