VPA程序，用于比较数组中的值并给出结果（MS Excel） [英] VPA program for compare values in array and give a result (MS Excel)

问题描述

我在数组中有一些变量。这些可以是是，否， NA或-。
例如，我有一个数组arr（1 t0 5）。值为是，是，否，否&是'。数组的长度可能会有所不同。像这些一样，鉴于上述四个值（是，否，不适用或-），组合可能会有所不同
我的要求是我想比较数组中的值并在Excel工作表的单元格。例如，如果数组中的所有值均为是或 NA，则结果应为是。如果任何一个值为否或-，则结果应为否。

I have some variables in an array. Those may be "Yes", "No", "NA" or "-". For example i have an array arr(1 t0 5) . The values would be 'Yes, Yes, No, No & Yes'. The length of array may vary. Like these the combination may vary given that above four values ("Yes", "No", "NA" or "-") My requirement is that i want to compare the values in array and give a result in a cell of excel work sheet. For example, if all values in array are either 'Yes' or 'NA' then result should be "Yes". If any one value is 'No' or '-' then result should be "No".

推荐答案

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

You can use Application.Match to find if you have at least 1 "No" in your arr array.

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

这篇关于VPA程序，用于比较数组中的值并给出结果（MS Excel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！