excel - 有没有办法跨行二维数组？

Question

使用 VBA，我想在 2D 数组中的每一行上进行 AND 并将结果添加到单独的 1D 数组中，而不需要对单对 AND 将结果与该行中的下一个项目进行 AND 运算。

仅供参考这是我第一次使用二维数组，如果有明显的解决方案，我很抱歉。

例如，如果我的工作表中的数据看起来像这样（实际范围要大得多）：

我想做一个excel公式的等量：=AND（B2：D2）然后=AND（B3：D3），等等......

我有设置所有内容的代码，但我不知道如何继续，除了循环遍历一行的每个元素，存储结果然后循环遍历下一个，等等。我希望有一个更好的（更有效）的方式进行。

到目前为止，这是我的代码

Sub Exceptions()
    ' Setup worksheet
    Dim wks As Worksheet
    Set wks = cnTest
        
    ' Find last row of range
    Dim LastRow As Long
    LastRow = Find_LastRow(wks)   'Functionthat returns last row
             
    ' load range into array
    Dim MyArray As Variant
    MyArray = wks.Range("B2:D8")
    
    ' Setup 1D Result array
    Dim Results As Variant
    Results = wks.Range("A2:A8")
    

    Dim i As Long
    For i = 1 To LastRow
        ' Perform AND function on each row of the array
        ' then place result in 1D array (Results())
        ' If this were a formul: =AND(B2:D2)
        '
        ' Is there way to "AND" across a row in and array or
        ' must I "AND" MyArray(1,1) with MyArray(1,2) then AND
        ' that result with MyArray(1,3)
    Next i
          
End Sub

谢谢

标签： excelvba

Answer 1

尝试这个。

Sub Exceptions()
    ' Setup worksheet

        

             
    ' load range into array
    Dim MyArray As Variant
    MyArray = ActiveSheet.Range("B2:D8")
    
    ' Setup 1D Result array
    Dim Results As Variant
    Results = ActiveSheet.Range("A2:A8")

    Dim i As Long
    Dim X As Long
    For i = 1 To UBound(MyArray, 1)
        Results(i, 1) = "True"
        For X = 1 To UBound(MyArray, 2)
            If MyArray(i, X) = False Then
                Results(i, 1) = "False"
                Exit For
            End If
        Next X
    Next i
End Sub