excel - 访问 range.areas(2) 中的值

Question

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

当我查看局部变量窗口时，我可以在范围对象的区域部分的第 2 项部分中找到范围的值。

我试图在两个范围区域之间建立一个联合，但结果仍然给我留下了一个带有区域的范围，并且当我将它应用于数组时不起作用。我尝试在范围对象的“项目 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