如何跳过 vba 代码中的"nan"或空白

Question

我有一张excel表，上面有一堆"；nan"；和空白单元格，我不希望我的for循环评估这些，如下图所示

[image][1]

这是我下面的代码，它将所有的空白单元格值都设为0，并扰乱了我的代码。

For j = LBound(ARINC_CHA_DATA_array, 2) To UBound(ARINC_CHA_DATA_array, 2)
For i = LBound(ARINC_CHA_DATA_array, 1) To UBound(ARINC_CHA_DATA_array, 1)

If ARINC_CHA_DATA_array(i, j) <> "" Or ARINC_CHA_DATA_array(i, j) = "nan" Then GoTo Ravi
...........code
...........code

End If

Ravi:
Next i
Next j
```[![enter image description here][2]][2]


[1]: https://i.stack.imgur.com/ip2B9.png
[2]: https://i.stack.imgur.com/bTDou.png

Answer 1

Select Case与If...Then...Else在数组中循环时的比较

• 使用较短的变量名使代码更可读
• GoTo在某种程度上是为错误处理而保留的，它的使用通常是可以避免的
• 与A = B OR C = D相反的是A <> B AND C <> D
  这就是为什么与Test3相比，Test2更受欢迎(不需要Else语句(
• 所有三个代码都做同样的事情：它们将范围中的值读取到数组中，然后在执行... code的数组元素中循环(目前没有(，并将数组中的"修改"值写回范围
• 注意：如果范围内有公式，运行此代码后，它们将被替换为值

Option Explicit
Sub Test1()

Dim rg As Range: Set rg = ActiveSheet.Range("A1:J10")
Dim Acd As Variant: Acd = rg.Value

Dim Item As Variant
Dim j As Long, i As Long
For j = LBound(Acd, 2) To UBound(Acd, 2)
For i = LBound(Acd, 1) To UBound(Acd, 1)
Item = Acd(i, j)
Select Case CStr(Item)
Case "", "nan"
' do nothing
Case Else
' ... code

End Select
Next i
Next j

rg.Value = Acd

End Sub
Sub Test2()

Dim rg As Range: Set rg = ActiveSheet.Range("A1:J10")
Dim Acd As Variant: Acd = rg.Value

Dim Item As Variant
Dim j As Long, i As Long
For j = LBound(Acd, 2) To UBound(Acd, 2)
For i = LBound(Acd, 1) To UBound(Acd, 1)
Item = Acd(i, j)
If CStr(Item) <> "" And CStr(Item) <> "nan" Then
' ... code

'Else ' you don't need this
' do nothing
End If
Next i
Next j

rg.Value = Acd

End Sub
Sub Test3()

Dim rg As Range: Set rg = ActiveSheet.Range("A1:J10")
Dim Acd As Variant: Acd = rg.Value

Dim Item As Variant
Dim j As Long, i As Long
For j = LBound(Acd, 2) To UBound(Acd, 2)
For i = LBound(Acd, 1) To UBound(Acd, 1)
Item = Acd(i, j)
If CStr(Item) = "" Or CStr(Item) = "nan" Then
' do nothing
Else
' ... code

End If
Next i
Next j

rg.Value = Acd

End Sub