我目前有两个数组,每个数组都包含范围。您将如何获得这两个数组的交集。换句话说,我想获得一个范围数组,该范围仅包含两个原始数组中都包含的范围。我尝试了.Intersect,但是据我了解,它不适用于数组。
array1:(Range(“ A1”),Range(“ B1”),Range(“ C1”))array2:(Range(“ A1”),Range(“ A2”),Range(“ A3”))
结果:(Range(“ A1”))
您可以使用此代码。想法是使用迭代器将数组合并到单个范围内Union
。然后,您可以使用内置的Intersect
。
Function IntersectArray(array1() As Range, array2() As Range) As Range
Dim unionRangeArray1 As Range, unionRangeArray2 As Range
Dim i As Integer
Dim lbound1 As Integer: lbound1 = LBound(array1)
Dim lbound2 As Integer: lbound2 = LBound(array2)
Set unionRangeArray1 = array1(lbound1)
Set unionRangeArray2 = array2(lbound2)
For i = lbound1 + 1 To UBound(array1)
Set unionRangeArray1 = Application.Union(unionRangeArray1, array1(i))
Next
For i = lbound2 + 1 To UBound(array2)
Set unionRangeArray2 = Application.Union(unionRangeArray2, array2(i))
Next
Set IntersectArray = Application.Intersect(unionRangeArray1, unionRangeArray2)
End Function
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句