Excel/Locate a NamedRange in a sheet
QUESTION: Good day Bob
How can I locate a Named Range in a sheet?
Let's say that I want to locate and return the cell address of the Named Range that contains "CellHeader*"?
I cannot be exact in the name since I would like to use this Sub() to search in several sheets and each sheet will have a cell named containing "CellHeader1" or "CellHeader2" or "CellHeader3"...
Hope this is clear enough...
ANSWER: Not sure I follow. If "CellHeader" in contained in cell D3, for example, would there possibly be a range named "xxx" which refers to range C2:E5 (containing D3)?
Or is the cell containing "CellHeader" a named range (single cell, like xxx's definition is just one cell)?
Are you looking for the name of the cell which has "cellHeader" in it? Might there be more than one cell on a sheet with contents of Cellheader?
Are you wanting this sub to find ALL names on ALL sheets for the contents of one of the cells in the range to be CellHeaderx?
See the confusion of what you're asking?
---------- FOLLOW-UP ----------
QUESTION: Sorry for not being clear enough...
Let's say that cell A14 in sheet1 has been named "CellHeader1"
Let's say that cell A18 in sheet2 has been named "CellHeader2"
Let's say that cell A77 in sheet3 has been named "CellHeader3"
People may add more lines above the Header so the Header is not static.
I want to be able to locate the Header in a given Sheet by finding the Named Range containing "CellHeader*".
In a more straightforward way, I need to return the address ($A$43) of the cell of a given Sheet that its Named Range starts with "CellHeader*".
Hoping that I'm more clear this time!
Create this VBA function:
Function Where() As String
For Each x In ThisWorkbook.Names
If LCase(x.Name) Like "cellheader*" Then If Range(x).Parent.Name = ActiveSheet.Name Then Where = Range(x).Address
then in any sheet, enter =Where() and it will return the address of the cell you want.