excel 如何查找指定范围内的所有内部范围

4uqofj5v 于 2023-03-31 发布在其他

关注(0)|答案(2)|浏览(203)

我不知道如何找到指定范围内的所有内部名称范围。
例如，存在三个范围：R_1、R_2和R_3。

我需要搜索所有的范围（对于每一个），只找到那些将适合在指定的范围内。
在这个例子中，指定的范围是R_2。在输出端，我需要找到R_2和R_3。但不是R_1。

Dim nm As Name

For Each nm In ThisWorkbook.Names
  *** CODE HERE ***
Next nm

我以前使用过以下代码。

Dim nm As Name
Dim rng As Range

Set rng = Range("R_2")  'specified range

For Each nm In ThisWorkbook.Names
  If Not Intersect(rng,Range(nm)) Is Nothing Then
    *** CODE HERE ***
  End If
Next nm

如果我需要找到所有的内部范围，但不存在外部范围，它就可以工作。也就是说，如果没有范围R_1，我找到R_2和R_3没有问题。但如果有一个外部范围，那么搜索交集是不合适的。
你能告诉我在这种情况下你能想到什么吗？我应该补充说，范围的名称可以不同，所以我需要确切地枚举所有范围（对于每个）。
原谅我的英语。它不是我的母语。
更新：
根据建议的答案，我为自己制定了一个解决方案：

Sub Test()
Dim nm As Name
Dim rng As Range

  Set rng = Range("R_2") 'example specified range

  For Each nm In ThisWorkbook.Names
    If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then 'ranges only from the active sheet
      If isInside(rng, nm) And Not rng.Name = nm Then Debug.Print nm.Name
    End If
  Next nm

End Sub

Function isInside(rb As Range, ra As Excel.Name) As Boolean
Dim r As Range
   
  Set r = Intersect(ra.RefersToRange, rb)

  If Not r Is Nothing Then
    isInside = (ra.RefersToRange.Cells.CountLarge = r.Cells.CountLarge)
    Exit Function
  End If

End Function

在我的项目中，这段代码完美地完成了任务，即使存在与边界相邻的范围。
第二个示例图像的链接：https://i.stack.imgur.com/rMh81.png
非常感谢所有对您的帮助做出回应的人！

excel

来源：https://stackoverflow.com/questions/75848079/how-to-find-all-internal-ranges-in-a-specified-range

2条答案

按热度按时间

d4so4syb1#

要找到命名区域的名称，您需要一个安全循环。从循环的核心，调用执行检查的函数。检查两个区域的交集的单元格数是否等于检查区域的单元格数。如果是，则控制区域完全在原始区域内。

Public Function getInnerRanges(specificRange As String) As String
   Dim nRng As Name, r As Range, rng As Range
   Set rng = Range(specificRange)
   getInnerRanges = specificRange
   For Each nRng In ThisWorkbook.Names
      If specificRange <> nRng.Name Then
         If isInside(rng, nRng) Then
            getInnerRanges = getInnerRanges & "," & nRng.Name
         End If
      End If
   Next
End Function

Public Function isInside(rb As Range, ra As Excel.Name) As Boolean
   Dim r As Range
   On Error GoTo Lerr
   Set r = Intersect(ra.RefersToRange, rb)
   If Not r Is Nothing Then
      isInside = (ra.RefersToRange.Cells.CountLarge = r.Cells.CountLarge)
      Exit Function
   End If
   On Error GoTo 0
   Exit Function
Lerr:
   On Error GoTo 0
   isInside = False
End Function

Sub example22()
   Dim s As String
   s = getInnerRanges("R_2")
   Debug.Print s
End Sub

赞(0）回复(0）举报 2023-03-31

pepwfjgg2#

一旦你有了角的坐标，就很容易进行比较了。第一个方法测试所有范围对所有范围。最后一个方法计算值。
你必须决定它是否应该与< and >或〈=和〉=进行比较。

Sub Test()
    Dim nm1 As Name, nm2 As Name
    
    For Each nm1 In ThisWorkbook.Names
        For Each nm2 In ThisWorkbook.Names
            If nm1.Name <> nm2.Name Then
                Debug.Print nm1.Name & " inside of " & nm2.Name & ": " & IsInside(nm1.RefersToRange, nm2.RefersToRange)
            End If
        Next nm2
    Next nm1
End Sub

Function IsInside(InnerRange As Range, OuterRange As Range) As Boolean
    Dim InnerLeft As Long
    Dim InnerTop As Long
    Dim InnerRight As Long
    Dim InnerBottom As Long
    Dim OuterLeft As Long
    Dim OuterTop As Long
    Dim OuterRight As Long
    Dim OuterBottom As Long
    
    With InnerRange
        InnerTop = .Row
        InnerLeft = .Column
        InnerBottom = .Row + .Rows.Count
        InnerRight = .Column + .Columns.Count
    End With
    With OuterRange
        OuterTop = .Row
        OuterLeft = .Column
        OuterBottom = .Row + .Rows.Count
        OuterRight = .Column + .Columns.Count
    End With
    IsInside = False
    If InnerLeft > OuterLeft And InnerRight < OuterRight Then
        If InnerTop > OuterTop And InnerBottom < OuterBottom Then
            IsInside = True
        End If
    End If
End Function

赞(0）回复(0）举报 2023-03-31

我来回答

excel 如何查找指定范围内的所有内部范围

2条答案

相关问题

热门标签

最新问答