You are here:

Excel/Locate a NamedRange in a sheet

Advertisement


Question
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...
Thank you

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!

Answer
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
   Next
End Function

then in any sheet, enter =Where() and it will return the address of the cell you want.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.