Union 函数不能跨越多个工作表(因为任何范围对象都包含在单个工作表对象中)。如果您想在一个循环中处理不同工作表上的多个范围,您需要考虑一种不同的策略,例如
Sub test()
Dim AllAreas(2) As Range, Idx As Integer, MyCell As Range, TargetRange As Range
Set AllAreas(0) = Worksheets("Sheet1").[C4]
Set AllAreas(1) = Worksheets("Sheet2").[D5]
Set AllAreas(2) = Worksheets("Sheet3").[E6]
Set TargetRange = Worksheets("Sheet4").[A1]
For Idx = 0 To 2
For Each MyCell In AllAreas(Idx).Cells
MyCell = "co-cooo!"
' combine in targetrange - each cell of any source range is put at same position
' in sheet 4 ... mind the precedence ... highest sheet highest prio
TargetRange(MyCell.Row, MyCell.Column) = MyCell
Next MyCell
Next Idx
End Sub
您可以通过最小值和最大值找到所有范围的叠加.Row
and .Column
范围数组中的所有范围,因此,如果您有一组复杂的规则来聚合部分重叠的范围,请从查找最小和最大角点开始,遍历该范围的所有单元格target范围并询问:区域 0, 1, 2, ... 中是否有一个值,如果有,则决定哪个值优先。
为了使事情变得更加优雅,您可以构建......
Type RngDef
Rng As Range
MinCol As Integer
MaxCol As Integer
MinRow As Integer
MaxRow As Integer
End Type
Sub test2()
Dim AllAreas(2) As RngDef, Idx As Integer, MyCell As Range, TargetRange As Range
Set AllAreas(0).Rng = Worksheets("Sheet1").[C4]
Set AllAreas(1).Rng = Worksheets("Sheet2").[D5]
Set AllAreas(2).Rng = Worksheets("Sheet3").[E6]
For Idx = 0 To 2
AllAreas(Idx).MinCol = AllAreas(Idx).Rng(1, 1).Column
AllAreas(Idx).MinRow = AllAreas(Idx).Rng(1, 1).Row
AllAreas(Idx).MaxCol = AllAreas(Idx).MinCol + AllAreas(Idx).Rng.Columns.Count - 1
AllAreas(Idx).MaxRow = AllAreas(Idx).MinRow + AllAreas(Idx).Rng.Rows.Count - 1
Next Idx
Set TargetRange = Worksheets("Sheet4").[A1]
End Sub
现在您已经掌握了所有范围及其边界......