我想在Excel VBA中测试给定单元格是否在给定范围内。最好的方法是什么?
来自帮助文档:
Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If
如果要测试的两个范围(你的给定单元格和你的给定范围)不在同一个工作表
中,则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
使用VBA在Microsoft Excel中确定单元格是否在范围内:
以下是引用的网站(保持对原提交者的致谢):
VBA宏提示由Erlandsen Data Consulting提供,提供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
我并不总是使用连续的范围。对于非连续的范围,我的解决方案如下(包括其他答案中的一些代码):
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
这里有另一种选项来查看单元格是否存在于范围内。如果您像我一样遇到了与交集解决方案相关的问题。
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是VBA函数之一,用于检查一个字符串是否存在于另一个字符串中。
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/instr-function
B2
是否在A1:C3
范围内时尝试一下。 - Abdallah El-Yaddak@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