如何在两个范围内查找重叠值

Question

我有两个范围。我试图找出它们的价值观是否存在重叠。是否有内置的"应用程序"函数可以查找两个范围内的值是否重叠？或者我是否需要做这样的事情，其中我有两组 for 循环和某种逻辑来测试rcellA(范围 A 中的单元格)是否与rcellB(范围 B 中的单元格)重叠？

Set rngA = loopset.range("A2-A999")
Set rngB = loopset.range("B2-B999")
For Each rcellA in rngA.cells
For Each rcellB in rngB.cells
Next rcellB
Next rcellA

Answer 1

我不明白你实际上在追求什么样的重叠

所以这里有一些镜头

返回两个区域中具有相同相对位置和值的单元格数：

Function CheckRangesOverlap1(rng1 As Range, rng2 As Range, overlaps As Long) As Boolean
Dim cell As Range
Dim firstRow As Long, firstColumn As Long, iRow As long, iColumn As Long
firstRow = rng1.Rows(1).Row - 1
firstColumn = rng1.Columns(1).Column - 1
For Each cell in rng1.SpecialCells(xlCellTypeConstants)
iRow = cell.Row - firstRow
iColumn = cell.Column - firstColumn
If rng2(iRow, iColumn).Value = cell.Value Then overlaps = overlaps + 1
Next
CheckRangesOverlap1 = overlaps > 0
End Function

返回两个区域中具有相同相对位置和值的单元格区域：

Function CheckRangesOverlap2(rng1 As Range, rng2 As Range, overlapRng As Range) As Boolean
Dim cell As Range
Dim firstRow As Long, firstColumn As Long, iRow As long, iColumn As Long
firstRow = rng1.Rows(1).Row - 1
firstColumn = rng1.Columns(1).Column - 1
Set overlapRng = rng1.Offset(rng1.Rows.Count).Resize(1,1)
For Each cell in rng1.SpecialCells(xlCellTypeConstants)
iRow = cell.Row - firstRow
IColumn = cell.Column - firstColumn
If rng2(iRow, iColumn).Value = cell.Value Then Set overlapRng = Union(overlapRng, cell)
Next
Set overlapRng = Intersect(overlapRng, rng1)
CheckRangesOverlap2 = Not overlapRng Is Nothing
End Function

您可以在游览中利用"主要"功能，如下所示：

Sub Main()
Dim loopset As Worksheet
Set loopset = WorkSheets("myLoopsetSheet")
With loopset
Set rngA = .range("A2-A999")
Set rngB = .range("B2-B999")
Dim overlaps As Long
If CheckRangesOverlap1(rngA, rngB, overlaps) Then
' your code to handle overlaps number
End If
Dim overlapRng As Range
If CheckRangesOverlap2(rngA, rngB, overlapRng) Then
' your code to handle overlapRng range
End If
End Sub