How to find defined cell name using VBA macro

Sorry for being off topic, but could CORBETTMJ please contact me via my email address, which can be found by clicking here https://www.experts-exchange.com/M_1892236.html

I am now looking for names for the first 15 rows.

Thanks,
sripri96

Hi Mark and Matt:

Thanks for your response. I am now looking for names for some rows in the sheet. I have no much knowledge about VBA, so any help would be greatly appreciated.

Thanks,
sripri96

Hi:

I am using the following code to check all the names in the workbook. Row 11 has a name. When the code checks for that name, it shows "='Report1'!Ref"

Dim nm As Name
For Each nm In ActiveWorkbook.Names
  If nm = "='Report1'!$11:$11" Then
    MsgBox ("did it")
  End If
Next

Any idea why it is happening?

Thanks once again,
sripri96

Hmm... you wouldn't have a rangename of "='Report1'!Ref" that would be the refers-to of the range.  You may want to change it to:

 If nm.RefersTo = "='Report1'!$11:$11" Then

If you're checking for rows with names, change my original code to:

 Dim nm As Name
 For Each nm In ActiveWorkbook.Names
  If nm.RefersToRange.Cells.Count = 256 And nm.RefersToRange.Rows.Count = 1 Then
   MsgBox nm.Name & " - " & nm.RefersToRange.Parent.Name & "!" & nm.RefersToRange.Address(0, 0)
  End If
 Next

Matt

Solution is:

Function yesno() As Boolean
pom = False
For i = 1 To Names.Count
pom = True
If Intersect(Range(Names(i)), ActiveCell) Is Nothing Then
pom = False
End If
If pom = True Then GoTo 10:
Next i
10:
yesno = pom
End Function

This function return that value is in some named range or not.
You can repleace ActiveCell with any range up to you.
The function return TRUE if your range is a part of named range