CODEWITHSUNDEEP
excelvba
user1495475
user1495475

Reputation: 1057

VBA:How to check cell range?

I have a cell name called Book.a,I have to check in if condition if the cell is in a range(B1:I10).How to do this? I tried 'intersect' method for this but didn`t work for me. Please suggest some answers.

Upvotes: 0

Views: 210

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim rng As Range

    With Sheets("Sheet1")
        On Error Resume Next
        Set rng = Application.Intersect(.Range("Book.a"), .Range("B1:I10"))
        On Error GoTo 0

        If Not rng Is Nothing Then
            MsgBox "Range `Book.a` is a part of `B1:I10`"
        Else
            MsgBox "Range `Book.a` not found or is not a part of `B1:I10`"
        End If
    End With
End Sub

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

It doesn't seem to be possible to search for named range within a range. So I take my comment back. What you can try is this: For each named range that is like book.a, you can check if its address intersect the given main range..

Dim objName As Name
Dim mainRange as Range

Sheet1.Unprotect
Set mainRange = ActiveWorkbook.Sheets(1).Range("B1:I10")
For Each objName In ActiveWorkbook.Names
    strName = objName.Name
    If InStr(1, strName, "book.a", vbTextCompare) > 0 Then
      If Intersect(objName.Address, mainRange) Is Nothing then
        '-- not within
      Else
        '-- within
      End If
    End If
Next
Sheet1.Protect

Upvotes: 0

Related Questions