VBA：如何将不同页面上的两个范围组合成一个循环 [英] VBA: How to combine two ranges on different sheets into one, to loop through

问题描述

尝试读取两个宽度相同但长度不同的两个范围，每个范围分别在不同的页面上，进入另一个范围，我需要以特定顺序循环组合的数据。

Trying to read two ranges of equal width but different lengths, each on different sheets, into another range which I need to loop through the combined data in a particular order.

Set wRIL = Worksheets("INS")
Set rRIL = wRIL.Range("L2")
Set rRIL = rRIL.CurrentRegion
Set rRIL = rRIL.Offset(1, 0).Resize(rRIL.Rows.Count - 1, rRIL.Columns.Count)

Set wROL = Worksheets("OUTS")
Set rROL = wROL.Range("N2")
Set rROL = rROL.CurrentRegion
Set rROL = rROL.Offset(1, 0).Resize(rROL.Rows.Count - 1, rROL.Columns.Count)

Set rRILROL = Union(rRIL, rROL)  

希望得到一个范围的大小rROL.Rows.Count + rRIL.Rows.Count long和rROL.Columns.Count宽。
该代码停止在联盟命令。

Hoping to get a range of size rROL.Rows.Count + rRIL.Rows.Count long and rROL.Columns.Count wide. This code stops at the Union command.

推荐答案

联盟功能不能跨多个工作表（因为任何范围对象由单个Worksheet对象包含）。如果要在一个循环中处理不同工作表上的多个范围，则需要考虑不同的策略，例如

The Union function cannot span multiple worksheets (as any range object is contained by a single Worksheet object). If you want to process multiple ranges on different sheets in one loop you need to think about a different strategy, e.g.

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 和 .Column 范围在范围数组中，因此如果您有一组复杂的规则来聚合重叠范围，请先查找最小和最大角落，穿过目标范围的所有单元格，并询问：是否在区域0，1，2，...中的值，如果是，则决定哪一个优先。

You can find the overlay of all ranges by the minimum and maximum .Row and .Column of all ranges within the array of ranges, so if you have a complex set of rules to aggregate parially overlapping ranges, start with finding min and max corners, run through all cells of the target range and ask: is there a value in area 0, 1, 2, ... and if so, then decide which one takes precedence.

为了使事情更加优雅，您可以构建..

To make things even more elegant you can build ...

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

现在你手中有所有范围和边界...

Now you have all ranges and their boundaries at hand ...

这篇关于VBA：如何将不同页面上的两个范围组合成一个循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！