我有多个具有范围的工作表,这些工作表收集到一个范围数组中,但我不能这样做,Union
因为它不适用于工作表。由于我想创建一个时间序列或FullSeriesCollection
基于合并范围内的元素的图表,因此我认为将范围数组重新划分为单个数组可能是我的解决方案。也许有一个我没有看到的更简单的解决方案。
我试图在下面概述一般情况。范围在我的代码中是动态的,但是在这里我只是随意设置它们。
Sub Collection()
Dim arrDate() As Variant
Dim arrRngTotal As Variant
Dim rng_1 As Range, rng_2 As Range, rng_3 As Range
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim j As Integer, k As Integer
Set ws_1 = ThisWorkbook.Sheets(1)
Set ws_2 = ThisWorkbook.Sheets(2)
' Example of ranges, not static in the original code.
Set rng_1 = ws_1.Range("A2:A10")
Set rng_2 = ws_1.Range("A11:A22")
Set rng_3 = ws_2.Range("A2:A22")
arrRngTotal = Array(rng_1.Value, rng_2.Value, rng_3.Value)
For k = LBound(arrRngTotal, 1) To UBound(arrRngTotal, 1)
For j = LBound(arrRngTotal(k), 1) To UBound(arrRngTotal(k), 1)
ReDim Preserve arrDate(j)
arrDate(j) = arrRngTotal(k)(j, 1)
Next j
Next k
End Sub
使用此代码时,我得到一个数组,其中包含22个元素,对应于的范围rng_3
。在嵌套循环之后,我想要得到的最终结果是一个数组,其中包含1个维度为1的单个数组中所有范围的元素。
我最终想要这样做的原因是我想使用数组值创建图表。
您的代码很好,唯一的错误是您正在重用内部循环中的相同变量,该变量将在每个外部循环上重置。为了解决这个问题,只需使用其他变量即可:
Sub Collection()
Dim arrDate() As Variant
Dim arrRngTotal As Variant
Dim rng_1 As Range, rng_2 As Range, rng_3 As Range
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim j As Integer, k As Integer, X As Long
Set ws_1 = ThisWorkbook.Sheets(1)
Set ws_2 = ThisWorkbook.Sheets(2)
' Example of ranges, not static in the original code.
Set rng_1 = ws_1.Range("A2:A10")
Set rng_2 = ws_1.Range("A11:A22")
Set rng_3 = ws_2.Range("A2:A22")
arrRngTotal = Array(rng_1.Value, rng_2.Value, rng_3.Value)
For k = LBound(arrRngTotal, 1) To UBound(arrRngTotal, 1)
For j = LBound(arrRngTotal(k), 1) To UBound(arrRngTotal(k), 1)
X = X + 1 'Add an additional counter
ReDim Preserve arrDate(X)
arrDate(X) = arrRngTotal(k)(j, 1)
Next j
Next k
End Sub
编辑:稍有变化,以根据@Tom建议提高速度...更多详细信息,请参见评论。
Sub Collection()
Dim arrDate() As Variant: ReDim arrDate(1 To 1)
Dim arrRngTotal As Variant
Dim rng_1 As Range, rng_2 As Range, rng_3 As Range
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim j As Integer, k As Integer, X As Long
Set ws_1 = ThisWorkbook.Sheets(1)
Set ws_2 = ThisWorkbook.Sheets(1)
' Example of ranges, not static in the original code.
Set rng_1 = ws_1.Range("A2:A10")
Set rng_2 = ws_1.Range("A11:A22")
Set rng_3 = ws_2.Range("A2:A22")
arrRngTotal = Array(rng_1.Value, rng_2.Value, rng_3.Value)
'Dimension the holding array outside the main data loop, unless you need to do this inside based on various conditions
For k = LBound(arrRngTotal, 1) To UBound(arrRngTotal, 1)
X = X + UBound(arrRngTotal(k))
Next k
ReDim Preserve arrDate(1 To X): X = 0
For k = LBound(arrRngTotal, 1) To UBound(arrRngTotal, 1)
For j = LBound(arrRngTotal(k), 1) To UBound(arrRngTotal(k), 1)
X = X + 1
arrDate(X) = arrRngTotal(k)(j, 1)
Next j
Next k
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句