访问range.areas中的值(2)

Question

我在VBA中将一个范围保存到数组中。除非我在范围内有多个区域，然后只有第一个区域被转换为数组，而第二个区域被忽略。

当我查看局部变量窗口时，我可以在range对象的Areas部分的Item 2部分找到range的值。

我试图在两个范围区域之间建立一个联合，但结果仍然给我留下了一个包含区域的范围，并且当我将其应用于数组时不起作用。我尝试在range对象的'Item 2‘部分访问这些值，但我不知道如何访问它们。

Sub Main()
    Dim InputRange As Range

    Set InputRange = Union(Sheets("VialHelper").Range("H91:GY94"), 
    Sheets("VialHelper").Range("H145:GY145"))
    Call ArrayBuilder(AwesomeArr(), InputRange)

End Sub


Sub ArrayBuilder(ByRef finArray() As Variant, RangeArr As Range)

    Dim tempArray As Variant
    Dim i As Integer, j As Integer, counter As Integer
    Dim area As Range

    counter = 1
    tempArray = RangeArr 'HERE IS WHERE I NEED THE SECOND AREA TO BE ADDED TO THE ARRAY


    ReDim finArray(1 To 2)
    For i = LBound(tempArray, 1) To UBound(tempArray, 1)
        For j = LBound(tempArray, 2) To UBound(tempArray, 2)
            If Left(tempArray(i, j), 2) = "L0" Or Left(tempArray(i, j), 2) = "R0" Then
                ReDim Preserve finArray(1 To counter)
                finArray(counter) = tempArray(i, j)
                counter = counter + 1
            End If
        Next j
    Next i

End Sub

我希望看到范围中定义的区域的所有值都在tempArray变量中。但我只看到来自区域1的值。

Answer 1

您需要以某种方式迭代这些区域。

可能是这样的：

Sub ArrayBuilder(ByRef finArray() As Variant, ByVal RangeArr As Range)

    Dim tempArray() As Variant
    Dim i As Long, j As Long, counter As Long

    counter = 1
    ReDim finArray(1 To 2) ' not sure why you do this

    Dim area As Range
    For Each area In RangeArr.Areas
        If area.Count = 1 Then
            ReDim tempArray(1 To 1, 1 To 1)
            tempArray(1, 1) = area.Value
        Else
            tempArray = area.Value
        End If

        For i = LBound(tempArray, 1) To UBound(tempArray, 1)
            For j = LBound(tempArray, 2) To UBound(tempArray, 2)
                If Left(tempArray(i, j), 2) = "L0" Or Left(tempArray(i, j), 2) = "R0" Then
                    ReDim Preserve finArray(1 To counter)
                    finArray(counter) = tempArray(i, j)
                    counter = counter + 1
                End If
            Next j
        Next i
    Next area

End Sub