试图将一个值与多个多维数组进行比较

Question

我有一个多列数据的电子表格。在VBA中，我试图根据其他三个列中的任何一个中的数据在每一行中设置一个值。它将始终是这些数据之间的OR比较，并且需要返回五个值中的一个。

所以我已经创建了五个数组(它们是多维的，因为它们是作为范围读取的，但是，如果需要的话，我想它们可以是一维的)，我打算使用一堆if - then语句将这三列与那些数组进行比较，以返回必要的值。

创建数组很容易，但我不知道如何正确创建IF-THEN过程。这个过程的一个例子是:

IF A1 is in ArrayA THEN
 D1="Dog"
ELSEIF A1 is in ArrayB THEN
 D1="Cat"
ELSEIF B1 is in ArrayC THEN
 D1="Bird"
ELSEIF B1 is in ArrayD THEN
 D1="Monkey"
ELSEIF C1 is in ArrayE THEN
 D1="Blue"
ELSE
 D1="Other"

我不确定这是否是完成我想做的事情的最有效的方法，所以我绝对愿意接受不同方法的建议。谢谢你。

Answer 1

好的，这是我如何使它工作。其中大部分来自另一个人，但他们删除了他们的评论，所以我不能感谢他们。

arrCols = Array(arrA, arrB, arrC, arrD, arrE)
arrVals = Array("Dog", "Cat", "Bird", "Monkey", "Blue")
For i = 2 To rCnt
    pID = Cells(i, 2).Value
    pName = Cells(i, 3).Value
    pGroup = Cells(i, 4).Value
    ans = ""
    For j = LBound(arrCols) To UBound(arrCols)
        If Not IsError(Application.Match(pGroup, arrCols(j), 0)) Then
            ans = arrVals(j)
            Exit For
        ElseIf Not IsError(Application.Match(pName, arrCols(j), 0)) Then
            ans = arrVals(j)
            Exit For
        ElseIf Not IsError(Application.Match(pID, arrCols(j), 0)) Then
            ans = arrVals(j)
            Exit For
        End If
    Next j
 Cells(i, 5) = IIf(ans = "", "Other", ans)
 Next i

我创建了一个数组的数组来搜索。i循环遍历所有行。j循环循环遍历主数组中的数组。三个IF语句用于检查三个不同的列。返回值默认为"Other"，除非在其中一个数组中找到它。

我能够添加到这段代码的唯一东西是检查行中三个不同列所需的东西。

Answer 2

下面是一个不循环

的示例

If Len("," & Join(ArrayA, ",") & ",") <> Len("," & Replace(Join(ArrayA, ","), "," & Range("A1").Value & ",", "") & ",") Then
    Range("D1").Formula = "Dog"
ElseIf Len("," & Join(ArrayB, ",") & ",") <> Len("," & Replace(Join(ArrayB, ","), "," & Range("A1").Value & ",", "") & ",") Then
    Range("D1").Formula = "Cat"
ElseIf Len("," & Join(ArrayC, ",") & ",") <> Len("," & Replace(Join(ArrayC, ","), "," & Range("A1").Value & ",", "") & ",") Then
    Range("D1").Formula = "Bird"
ElseIf Len("," & Join(ArrayD, ",") & ",") <> Len("," & Replace(Join(ArrayD, ","), "," & Range("A1").Value & ",", "") & ",") Then
    Range("D1").Formula = "Monkey"
ElseIf Len("," & Join(ArrayE, ",") & ",") <> Len("," & Replace(Join(ArrayE, ","), "," & Range("A1").Value & ",", "") & ",") Then
    Range("D1").Formula = "Blue"
Else
    Range("D1").Formula = "Other"
End If

将数组连接到一个字符串，然后将关键字(Range("A1"))替换为空，根据字符串的len检查len。在两边加一个逗号，以确保它是完整的单词(不要希望像Catfish这样的东西返回为Cat)

用InStr代替也是一样的:

If InStr(1, "," & Join(ArrayA, ",") & ",", Range("A1").Value) > 0 Then
    Range("D1").Formula = "Dog"
ElseIf InStr(1, "," & Join(ArrayB, ",") & ",", Range("A1").Value) > 0 Then
    Range("D1").Formula = "Cat"
ElseIf InStr(1, "," & Join(ArrayC, ",") & ",", Range("A1").Value) > 0 Then
    Range("D1").Formula = "Bird"
ElseIf InStr(1, "," & Join(ArrayD, ",") & ",", Range("A1").Value) > 0 Then
    Range("D1").Formula = "Monkey"
ElseIf InStr(1, "," & Join(ArrayE, ",") & ",", Range("A1").Value) > 0 Then
    Range("D1").Formula = "Blue"
Else
    Range("D1").Formula = "Other"
End If