VBA 测试单元格是否在一个范围内
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5183374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA test if cell is in a range
提问by loveforvdubs
I want to test if a given cell is within a given range in Excel VBA. What is the best way to do this?
我想测试给定的单元格是否在 Excel VBA 中的给定范围内。做这个的最好方式是什么？
回答by Patrick Honorez
From the Help:
从帮助：
Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If
回答by sancho.s ReinstateMonicaCellio
If the two ranges to be tested (your given celland your given range) are not in the same Worksheet, then Application.Intersectthrows an error. Thus, a way to avoid itis with something like
如果要测试的两个范围（给定的单元格和给定的范围）不相同Worksheet，则Application.Intersect抛出错误。因此，避免它的一种方法是使用类似
Sub test_inters(rng1 As Range, rng2 As Range)
If (rng1.Parent.Name = rng2.Parent.Name) Then
Dim ints As Range
Set ints = Application.Intersect(rng1, rng2)
If (Not (ints Is Nothing)) Then
' Do your job
End If
End If
End Sub
回答by mwolfe02
Determine if a cell is within a range using VBA in Microsoft Excel:
在 Microsoft Excel 中使用 VBA 确定单元格是否在范围内：
From the linked site (maintaining credit to original submitter):
从链接站点（保持对原始提交者的信用）：
VBA macro tip contributed by Erlandsen Data Consultingoffering Microsoft Excel Application development, template customization, support and training solutions
由Erlandsen Data Consulting提供的VBA 宏提示提供 Microsoft Excel 应用程序开发、模板定制、支持和培训解决方案
Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
End Function
Sub TestInRange()
If InRange(ActiveCell, Range("A1:D100")) Then
' code to handle that the active cell is within the right range
MsgBox "Active Cell In Range!"
Else
' code to handle that the active cell is not within the right range
MsgBox "Active Cell NOT In Range!"
End If
End Sub
回答by Jimit Rupani
@mywolfe02 gives a static range code so his inRange works fine but if you want to add dynamic range then use this one with inRange function of him.this works better with when you want to populate data to fix starting cell and last column is also fixed.
@mywolfe02 给出了一个静态范围代码，所以他的 inRange 工作正常，但如果你想添加动态范围，然后使用他的 inRange 函数。当你想填充数据以修复起始单元格和最后一列也被修复时，这更有效.
Sub DynamicRange()
Dim sht As Worksheet
Dim LastRow As Long
Dim StartCell As Range
Dim rng As Range
Set sht = Worksheets("xyz")
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Set rng = Workbooks("Record.xlsm").Worksheets("xyz").Range(Cells(12, 2), Cells(LastRow, 12))
Debug.Print LastRow
If InRange(ActiveCell, rng) Then
' MsgBox "Active Cell In Range!"
Else
MsgBox "Please select the cell within the range!"
End If
End Sub
回答by Roskow
Here is another option to see if a cell exists inside a range. In case you have issues with the Intersect solution as I did.
这是查看单元格是否存在于范围内的另一个选项。如果您像我一样遇到 Intersect 解决方案的问题。
If InStr(range("NamedRange").Address, range("IndividualCell").Address) > 0 Then
'The individual cell exists in the named range
Else
'The individual cell does not exist in the named range
End If
InStr is a VBA function that checks if a string exists within another string.
InStr 是一个 VBA 函数，用于检查一个字符串是否存在于另一个字符串中。
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/instr-function
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/instr-function
回答by Jeremy H.
I don't work with contiguous ranges all the time. My solution for non-contiguous ranges is as follows (includes some code from other answers here):
我并不总是使用连续的范围。我对非连续范围的解决方案如下（包括此处其他答案中的一些代码）：
Sub test_inters()
Dim rng1 As Range
Dim rng2 As Range
Dim inters As Range
Set rng2 = Worksheets("Gen2").Range("K7")
Set rng1 = ExcludeCell(Worksheets("Gen2").Range("K6:K8"), rng2)
If (rng2.Parent.name = rng1.Parent.name) Then
Dim ints As Range
MsgBox rng1.Address & vbCrLf _
& rng2.Address & vbCrLf _
For Each cell In rng1
MsgBox cell.Address
Set ints = Application.Intersect(cell, rng2)
If (Not (ints Is Nothing)) Then
MsgBox "Yes intersection"
Else
MsgBox "No intersection"
End If
Next cell
End If
End Sub
