VPA程序，用于比较数组中的值并给出结果(MS Excel)

Question

我在数组中有一些变量。这些可能是"是"、"否"、"不适用"或"-"。例如，我有一个数组 arr(1 t0 5( .值将为"是，是，否，否和是"。数组的长度可能会有所不同。像这些，鉴于上述四个值("是"，"否"，"NA"或"-"(的组合可能会有所不同我的要求是我想比较数组中的值并在 excel 工作表的单元格中给出结果。例如，如果数组中的所有值都是"是"或"NA"，则结果应为"是"。如果任何一个值为"否"或"-"，则结果应为"否"。

Answer 1

您可以使用Application.Match来查找arr数组中是否至少有 1 个"否"。

Option Explicit
Sub CheckArray()
Dim arr As Variant
Dim Res As Variant, r As Variant
Dim Result As String   
' test #1: at least 1 "No" or "-"  in array
arr = Array("Yes", "-", "Yes", "NA", "Yes")
Res = Application.Match(Array("No", "-"), arr, 0)
For Each r In Res
    Result = "Yes" ' init value
    If Not IsError(r) Then ' <-- at least 1 result of "-" or "No" found
        Result = "No"
        Exit For
    End If
Next r    
' === The result is "No" ===

' test #2: there are no "No" or "-" in the array
arr = Array("Yes", "NA", "Yes", "NA", "Yes")
Res = Application.Match(Array("No", "-"), arr, 0)
For Each r In Res
    Result = "Yes" ' init value
    If Not IsError(r) Then ' <-- at least 1 result of "-" or "No" found
        Result = "No"
        Exit For
    End If
Next r
' === The result is "Yes" ===
End Sub